import ast

import requests

import pandas as pd
import base64
import json
import os
import shutil
import time
import random
from collections import OrderedDict
from urllib.parse import quote_plus
import redis
import requests
from sqlalchemy import create_engine
from DrissionPage import ChromiumPage
from DrissionPage import ChromiumOptions
from DrissionPage.common import Settings
from DrissionPage.common import Keys
from DrissionPage.common import Actions
import pymysql
from DrissionPage.errors import *

import datetime


"""
https://customer.xiaohongshu.com/api/cas/customer/web/qr-code?service=https:%2F%2Fcreator.xiaohongshu.com&qr_code_id=68c517395516719772176435"""

class DY_XPIDER:
    def __init__(self,user_id):
        self.CO = ChromiumOptions()  ##创建chrome实例
        # self.user_id = self.user_input()  ##用户
        self.user_id = str(user_id)  ##用户

        self.PORT = self.port_input()  ##chrome端口
        self.CO.set_paths(browser_path="browser_path='/Applications/Google Chrome.app/Contents/MacOS/Google Chrome")  ##chrome启动路径
        self.CO.set_local_port(self.PORT)  ##生成的端口 这里是手动生成
        self.CO.set_user_data_path(os.getcwd() + '/user_info_dir/{}'.format(self.user_id))  ##用户chrome文件夹
        # self.CO.arguments.append('--headless')
        self.ch = ChromiumPage(self.CO)


        self.ch.set.window.max()  ##chrome窗口最大化
        self.ac = Actions(self.ch)
        # self.fans = self.xhs_user_info_requests()
        # print(self.user_id)
        # self.redis_conn = redis.StrictRedis(host='r-bp162522qrwjh5e7jhpd.redis.rds.aliyuncs.com', port=6379,
        #                                     password='123sdmmh568%$#vs*!', db=21)
        self.redis_conn = redis.StrictRedis(host='r-bp1y4wxs1fa8vxd1xspd.redis.rds.aliyuncs.com', port=6379,
                                            password='Jison0820', db=0)

    """根据用户ID查出端口号"""

    def port_fe(self):
        sql = "select spider_user_id,spider_port,spider_login_code from dy_creator_center where spider_user_id='{}'".format(
            self.user_id)
        spider_config = self.pymysql_fetchall(sql)
        port = []
        for i in spider_config:
            port.append(i[1])

        return ''.join(port)

    """自动输入端口号"""

    def port_input(self):

        port1 = random.randint(5000, 9000)

        cc = self.port_jiance()
        while True:
            cc = self.port_jiance()
            if cc == "用户存在":
                # print("当前用户存在")
                port_ = self.port_fe()
                # print(port_)
                return port_
            if cc == "端口不存在":
                # print(port1)
                return port1
            else:
                return self.PORT

    """自动随机生成端口 并进行检测"""

    def port_jiance(self):
        port1 = random.randint(5000, 9000)

        sql = "select spider_user_id,spider_port,spider_login_code from dy_creator_center"
        spider_config = self.pymysql_fetchall(sql)
        port_list = []
        # print(spider_config)
        for i in spider_config:
            port_list.append(i[1])
        user_exists = self.user_state()
        # print(user_exists, '************')
        if user_exists == "用户不存在":
            if port1 not in port_list:
                return "端口不存在"
            else:
                return "端口存在"
        else:
            return '用户存在'

    """查询数据库"""

    def pymysql_fetchall(self, sql):

        con = pymysql.connect(host='rm-bp1be6s3581xp13rfco.mysql.rds.aliyuncs.com', port=3306, user='root',
                              passwd='Jison0820', db='data-warehouse', charset='utf8')
        # 获取操作数据的对象 cursor
        cursor = con.cursor()
        cursor.execute(sql)
        sql_result = cursor.fetchall()
        # 提交事务
        con.commit()
        # 关闭Cursor
        cursor.close()
        # 关闭链接
        con.close()

        return sql_result

    """从数据库判断用户是否存在"""

    def user_state(self):
        sql = "select spider_user_id,spider_port,spider_login_code from dy_creator_center"
        result = self.pymysql_fetchall(sql)
        user_list = []
        for i in result:
            user_list.append(i[0])
        # print(user_list)
        if self.user_id in user_list:
            # print("存在")
            return "用户存在"
        else:
            return "用户不存在"

    """判断用户是否在数据库中 不在为新用户 在的话为老用户"""

    def user_info(self):
        engine2 = self.mysql_config()
        data_frame = pd.DataFrame([{"spider_user_id": str(self.user_id), "spider_port": str(self.PORT)}])
        # print(data_frame)
        # pd.io.sql.to_sql(data_frame, 'xhs_user_info', engine2, schema='live_data',if_exists='append',index=False)
        return data_frame


    # def mysql_config(self):
    #     user = 'live_data'
    #     password = quote_plus('CJMg2tsHg7sj6bo0UTKq0a4T!fPDpJ')
    #     # passwd ='merchantsasd123!@%&'
    #     host = 'rm-bp17r99475xuauk63yo.mysql.rds.aliyuncs.com'
    #     # port1 ='3306'
    #     dbname2 = 'live_data'
    #     engine2 = create_engine(f"mysql+pymysql://{user}:{password}@{host}:3306/{dbname2}?charset=utf8mb4")
    #     return engine2


    def mysql_config(self):
        user = 'root'
        password = quote_plus('Jison0820')
        # passwd ='merchantsasd123!@%&'
        host = 'rm-bp1be6s3581xp13rfco.mysql.rds.aliyuncs.com'
        # port1 ='3306'
        dbname2 = 'data-warehouse'
        engine2 = create_engine(f"mysql+pymysql://{user}:{password}@{host}:3306/{dbname2}?charset=utf8mb4")
        return engine2


    """修改用户登录状态的mysql"""

    def pymysql_update(self, sql, num):

        con = pymysql.connect(host='rm-bp1be6s3581xp13rfco.mysql.rds.aliyuncs.com', port=3306, user='root',
                              passwd='Jison0820', db='data-warehouse', charset='utf8')
        # 获取操作数据的对象 cursor
        cursor = con.cursor()
        cursor.execute(sql, (num, self.user_id))
        # sql_result = cursor.fetchall()
        # 提交事务
        con.commit()
        # 关闭Cursor
        cursor.close()
        # 关闭链接
        con.close()

        return "用户{}状态修改成功,当前状态值为{}".format(self.user_id, num)

    """修改用户三十天数据"""
    def pymysql_update_total(self, sql):

        con = pymysql.connect(host='rm-bp17r99475xuauk63yo.mysql.rds.aliyuncs.com', port=3306, user='root',
                              passwd='CJMg2tsHg7sj6bo0UTKq0a4T!fPDpJ', db='data-warehouse', charset='utf8mb4')
        # 获取操作数据的对象 cursor
        cursor = con.cursor()
        cursor.execute(sql,)
        # sql_result = cursor.fetchall()
        # 提交事务
        con.commit()
        # 关闭Cursor
        cursor.close()
        # 关闭链接
        con.close()

        return "用户{}值成功,当前状态值".format(self.user_id)

    """登录二维码状态监控"""
    def qrcode_save(self):
        print("二维码获取中")
        douyin_qrcode_text = self.ch.ele('xpath://*[@id="sub-app"]/div/div[2]/div[3]/div/div[2]/div/div[2]/div[2]/div[1]/img[1]')
        print(douyin_qrcode_text)
        douyin_qrcode_redis = str(douyin_qrcode_text).split("<ChromiumElement img src=")[1].split(">")[0].split('\n')[0]
        img_base = douyin_qrcode_redis.encode("utf-8")
        self.redis_conn.set('douyin_qrcode:{}'.format(self.user_id), img_base)
        print("写入成功")
        self.redis_conn.expire('douyin_qrcode:{}'.format(self.user_id), 180)

    def login_stauts_listen(self):
        self.ch.listen.start('https://sso.douyin.com/check_qrconnect')

        login_status_list = []

        for packet in self.ch.listen.steps():
            # print(packet.url)

            try:
                status_value = str(packet.response.body["data"]["status"])
                if status_value == "1":
                    self.redis_conn.set('dy_center_qrcode_status:{}'.format(self.user_id), '1')
                    self.redis_conn.expire('dy_center_qrcode_status:{}'.format(self.user_id), 300)
                    print("等待扫码中")

                if status_value == "2":
                    self.redis_conn.set('dy_center_qrcode_status:{}'.format(self.user_id), '2')
                    self.redis_conn.expire('dy_center_qrcode_status:{}'.format(self.user_id), 300)
                    print("扫码成功")

                if status_value == "3":
                    self.redis_conn.set('dy_center_qrcode_status:{}'.format(self.user_id), '3')
                    self.redis_conn.expire('dy_center_qrcode_status:{}'.format(self.user_id), 300)
                    print("登录成功")
                    break

                # print("二维码状态",status_value)
                if status_value == '5':
                    self.redis_conn.set('dy_center_qrcode_status:{}'.format(self.user_id), '5')
                    self.redis_conn.expire('dy_center_qrcode_status:{}'.format(self.user_id), 300)
                    print("扫码超时或授权超时")
                    return "扫码超时或授权超时"

            except:
                if packet.response.body["verify_scene_desc"] == "你的登录可能存在风险，为保护账号安全，请进行安全验证":
                    time.sleep(1)
                    self.redis_conn.set('dy_center_qrcode_status:{}'.format(self.user_id), '6')
                    self.redis_conn.expire('dy_center_qrcode_status:{}'.format(self.user_id), 300)
                    print("需要短信验证登录")
                    self.ch.ele('xpath://*[@id="uc-second-verify"]/div/div/article/div[2]/div/div[1]/div/div[1]/p').click()
                    time.sleep(0.5)
                    #点击获取验证码按钮
                    self.ch.ele('xpath://*[@id="uc-second-verify"]/div/div/article/div[2]/div/div/div[2]/p').click()
                    cu_time = datetime.datetime.now()
                    while True:
                        value = self.redis_conn.get('dy_message_qrcode:{}'.format(self.user_id))
                        # print(value)
                        if value == None:
                            en_time = datetime.datetime.now()
                            all_time = en_time - cu_time
                            if all_time.seconds > 60:
                                return "手机验证码超时，请重新授权"
                            print("正在获取验证码")
                        else:
                            print("获取到了验证码")
                            print(value)
                            self.ch.ele('xpath://*[@id="uc-second-verify"]/div/div/article/div[2]/div/div/div[1]/input').input('{}\n'.format(value))
                            break

                    time.sleep(1)
                    self.ch.ele('xpath://*[@id="uc-second-verify"]/div/div/article/div[3]/div/div[2]').click()

                    #输入验证码
                    # //*[@id="uc-second-verify"]/div/div/article/div[2]/div/div/div[1]/input
                    ##验证按钮
                    #//*[@id="uc-second-verify"]/div/div/article/div[3]/div/div[2]
            print(packet.response.body)
            print(packet.url)

        return "抖音创作中心用户登录成功"


    """抖音创作者中心用户详情获取"""

    def dy_user_info_detail(self):
        self.ch.refresh()
        "https://creator.douyin.com/aweme/v1/creator/user/info/"
        self.ch.listen.start('https://creator.douyin.com/aweme/v1/creator/user/info')

        user_fans_list = []
        nick_name_list = []
        douyin_unique_id_list = []

        for packet in self.ch.listen.steps(timeout=2):
            user_info = packet.response.body
            douyin_unique_id = user_info["douyin_user_verify_info"]["douyin_unique_id"]
            douyin_unique_nickname = user_info["douyin_user_verify_info"]["nick_name"]
            user_fans = user_info["douyin_user_verify_info"]["follower_count"]
            user_fans_list.append(user_fans)
            nick_name_list.append(douyin_unique_nickname)
            douyin_unique_id_list.append(douyin_unique_id)
            # print(douyin_unique_nickname)
            # print(douyin_unique_id)

            # print(user_info)

        user_sql = "select douyin_account_id from data_warehouse_douyin_account_data"
        user_sql_list = self.pymysql_fetchall(user_sql)
        user_list = []
        for i in user_sql_list:
            user_list.append(i[0])
        if douyin_unique_id_list[0] in user_list:
            print("该用户存在")
        else:
            curr_time = datetime.datetime.now()

            user_info_json = {"dy_spider_user_id":self.user_id,"nick_name":nick_name_list[0],"douyin_unique_id":douyin_unique_id_list[0]}

            user_info_account_json = {"nickname": nick_name_list[0], "douyin_account_id": douyin_unique_id_list[0],
                                      "douyin_code": douyin_unique_id_list[0], "data_view_capability_status": 1,
                                      "create_time":curr_time,"update_time":curr_time}
            user_info_account_json_frame = pd.DataFrame([user_info_account_json])


            user_info_frame = pd.DataFrame([user_info_json])
            pd.io.sql.to_sql(user_info_frame, 'dy_user_info', self.mysql_config(), schema='data-warehouse',if_exists='append',index=False)
            print("********---数据插入成功---********")
            pd.io.sql.to_sql(user_info_account_json_frame, 'data_warehouse_douyin_account_data', self.mysql_config(), schema='data-warehouse',if_exists='append',index=False)

        return user_fans_list[0],douyin_unique_id_list[0]

    """获取cookies"""
    def get_cookies(self):
        cookies_str = ''
        for i in self.ch.cookies(as_dict=False, all_domains=False, all_info=True):
            my_ordered_dict = OrderedDict(i)

            # 取出前两个键值对
            first_two_items = list(my_ordered_dict.items())[:2]
            key_value_str = ''
            count_key_value = 0
            for i in first_two_items:
                count_key_value = count_key_value + 1
                if count_key_value == 1:
                    key_value_str = key_value_str + i[1] + '='
                else:
                    key_value_str = key_value_str + i[1]
            cookies_str = cookies_str + key_value_str + ";"
        return cookies_str


    """数据中心 by_day数据处理"""
    def update_and_flatten_data(self,data):
        updated_data = {}
        for key, value_list in data.items():
            # print(value_list[0])
            # 假设每个键下的列表只有一个元素
            item = value_list[0]
            # item['value'] = item['douyin_value']
            try:
                del item['xigua_value']
                del item['douyin_value']
            # print(item)
                del item['date_time']
            except:
                pass
            for k, va in item.items():
                # print(k,va)
                result = {key: va}
                # print(result)
                updated_data.update(result)
        return updated_data


    ##获取前一天时间
    def get_before_date(self):

        from datetime import datetime, timedelta

        # 获取当前时间
        current_time = datetime.now()

        # 计算前一天的时间
        previous_day_time = current_time - timedelta(days=1)

        # 打印结果
        print("当前时间:", current_time)
        print("前一天时间:", previous_day_time)

        return previous_day_time

    """获取数据中心前一天数据接口"""
    def data_center_video_count_by_day(self, cookies,douyin_unique_id):
        current_time = datetime.datetime.now()

        user_json = {"douyin_account_id":douyin_unique_id,"update_time":current_time,"date":self.get_before_date()}

        # current_time = str(current_time)[:-7]
        url = 'https://creator.douyin.com/janus/douyin/creator/data/overview/dashboard'

        headers = {
            "Cookie": cookies
        }

        data = {

            "recent_days": 1

        }
        re = requests.get(url, json=data, headers=headers)

        total_center_json = re.json()
        total_value_json = {}
        trends_value_json = {}
        print(total_center_json)
        for i in total_center_json["metrics"]:
            if i=={}:
                continue

            total_value = {i["english_metric_name"]: i["metric_value"]}
            total_value_json.update(total_value)
            trends_value = {i['english_metric_name']:i["trends"]}
            trends_value_json.update(trends_value)

        # print(trends_value_json)
        by_day_json = self.update_and_flatten_data(trends_value_json)
        by_day_json.update(user_json)

        """趋势图数据计算"""

        delete_sql_trends = "DELETE FROM data_warehouse_douyin_video_by_day_data WHERE douyin_account_id='{}'".format(douyin_unique_id)
        self.pymysql_fetchall(delete_sql_trends)
        print("删除数据成功,等待插入最新数据")


        trends_frame = pd.DataFrame([by_day_json])
        # trends_frame = trends_frame.astype("str")
        trends_frame.rename(columns = {"play_cnt":"play_count","total_fans_cnt":"total_fans","cover_click_ratio":"cover_click_rate","cover_ratio":"cover_setting_rate","cancel_fans_cnt":"take_off_fans",
                                                      "net_fans_cnt":"net_growth_fans","comment_cnt":"works_comment","share_count":"works_share","homepage_view_cnt":"homepage_access","digg_cnt":"works_like"},inplace = True)
        delete_sql_trends = "DELETE FROM data_warehouse_douyin_video_by_day_data WHERE douyin_account_id='{}'".format(
            douyin_unique_id)
        self.pymysql_fetchall(delete_sql_trends)
        pd.io.sql.to_sql(trends_frame, 'data_warehouse_douyin_video_by_day_data', self.mysql_config(), schema='data-warehouse',
                         if_exists='append',
                         index=False)

        print("最新数据插入成功")


    def get_seven_thirty(self,data):
        simplified_data = {}
        # 遍历原始数据的每个类别
        for category, entries in data.items():
            simplified_entries = []
            # 遍历每个条目并只保留 value
            for entry in entries:
                simplified_entries.append(entry['value'])
                # 将简化后的条目存储在新的字典中
            simplified_data[category] = simplified_entries

        # 打印简化后的数据
        # print(simplified_data)
        return simplified_data

    """数据中心7天数据处理，加上日期"""
    def add_seven_date(self, data_list):
        from datetime import datetime, timedelta

        # 假设这是当前时间的前一天（因为要求是从前一天开始算）
        # 注意：这里我们使用了一个固定的日期作为示例，但在实际应用中，你应该使用datetime.now() - timedelta(days=1)来获取实际的前一天
        current_day_minus_one = datetime.now()  # 示例日期，请替换为datetime.now() - timedelta(days=1)的实际结果

        # 计算前7天的日期，并以倒序方式添加到每个字典中
        for i, data in enumerate(reversed(data_list)):
            # 计算日期，注意这里i是从0开始的，所以要加1来得到正确的天数偏移
            date = (current_day_minus_one - timedelta(days=i + 1)).strftime('%Y-%m-%d')
            data['date'] = date

        # 由于我们是倒序遍历的，所以最终data_list中的顺序是正确的（即日期是倒序的）
        # 打印结果查看

        date_result_list = []
        for data in data_list:
            date_result_list.append(data)

        return date_result_list

    """获取数据中心7天数据接口"""
    def data_center_video_count_seven(self, cookies, douyin_unique_id):
        current_time = datetime.datetime.now()


        user_json = {"douyin_account_id": douyin_unique_id, "update_time": str(current_time), "date": self.get_before_date()}

        # current_time = str(current_time)[:-7]
        url = 'https://creator.douyin.com/janus/douyin/creator/data/overview/dashboard'

        headers = {
            "Cookie": cookies
        }

        data = {

            "recent_days": 7

        }
        re = requests.get(url, json=data, headers=headers)

        total_center_json = re.json()
        total_value_json = {}
        trends_value_json = {}
        for i in total_center_json["metrics"]:
            total_value = {i["english_metric_name"]: i["metric_value"]}
            total_value_json.update(total_value)
            trends_value = {i['english_metric_name']: i["trends"]}
            trends_value_json.update(trends_value)

        seven_json = self.get_seven_thirty(trends_value_json)
        # seven_json.update(user_json)

        # 获取所有键的列表
        keys = seven_json.keys()

        # 假设所有列表长度相同，我们可以取第一个列表的长度作为循环次数
        num_entries = len(seven_json['play_cnt'])

        # 拆分数据
        entries = []
        for i in range(num_entries):
            entry = {}
            for key in keys:
                entry[key] = seven_json[key][i]
            entries.append(entry)

            # 打印拆分后的数据
        json_list = []
        for entry in entries:
            entry.update(user_json)
            json_list.append(entry)

        seven_json_result = self.add_seven_date(json_list)
        # print(seven_json_result)
        seven_frame = pd.DataFrame(seven_json_result)
        seven_frame.rename(
            columns={"play_cnt": "play_count", "total_fans_cnt": "total_fans", "cover_click_ratio": "cover_click_rate",
                     "cover_ratio": "cover_setting_rate", "cancel_fans_cnt": "take_off_fans",
                     "net_fans_cnt": "net_growth_fans", "comment_cnt": "works_comment", "share_count": "works_share",
                     "homepage_view_cnt": "homepage_access", "digg_cnt": "works_like"}, inplace=True)
        print(seven_frame)

        delete_sql_trends = "DELETE FROM data_warehouse_douyin_video_by_seven_data WHERE douyin_account_id='{}'".format(douyin_unique_id)
        self.pymysql_fetchall(delete_sql_trends)
        pd.io.sql.to_sql(seven_frame, 'data_warehouse_douyin_video_by_seven_data', self.mysql_config(),
                         schema='data-warehouse',
                         if_exists='append',
                         index=False)
        print("最新数据插入成功")

    """获取数据中心30天数据接口"""

    def data_center_video_count_thirty(self, cookies, douyin_unique_id):
        current_time = datetime.datetime.now()

        user_json = {"douyin_account_id": douyin_unique_id, "update_time": str(current_time),
                     "date": self.get_before_date()}

        # current_time = str(current_time)[:-7]
        url = 'https://creator.douyin.com/janus/douyin/creator/data/overview/dashboard'

        headers = {
            "Cookie": cookies
        }

        data = {

            "recent_days": 30

        }
        re = requests.get(url, json=data, headers=headers)

        total_center_json = re.json()
        total_value_json = {}
        trends_value_json = {}
        for i in total_center_json["metrics"]:
            total_value = {i["english_metric_name"]: i["metric_value"]}
            total_value_json.update(total_value)
            trends_value = {i['english_metric_name']: i["trends"]}
            trends_value_json.update(trends_value)

        seven_json = self.get_seven_thirty(trends_value_json)
        # seven_json.update(user_json)

        # 获取所有键的列表
        keys = seven_json.keys()

        # 假设所有列表长度相同，我们可以取第一个列表的长度作为循环次数
        num_entries = len(seven_json['play_cnt'])

        # 拆分数据
        entries = []
        for i in range(num_entries):
            entry = {}
            for key in keys:
                entry[key] = seven_json[key][i]
            entries.append(entry)

            # 打印拆分后的数据
        json_list = []
        for entry in entries:
            entry.update(user_json)
            json_list.append(entry)

        seven_json_result = self.add_seven_date(json_list)
        # print(seven_json_result)
        seven_frame = pd.DataFrame(seven_json_result)
        seven_frame.rename(
            columns={"play_cnt": "play_count", "total_fans_cnt": "total_fans", "cover_click_ratio": "cover_click_rate",
                     "cover_ratio": "cover_setting_rate", "cancel_fans_cnt": "take_off_fans",
                     "net_fans_cnt": "net_growth_fans", "comment_cnt": "works_comment", "share_count": "works_share",
                     "homepage_view_cnt": "homepage_access", "digg_cnt": "works_like"}, inplace=True)
        # print(seven_frame)

        delete_sql_trends = "DELETE FROM data_warehouse_douyin_video_by_thirty_data WHERE douyin_account_id='{}'".format(douyin_unique_id)
        self.pymysql_fetchall(delete_sql_trends)
        pd.io.sql.to_sql(seven_frame, 'data_warehouse_douyin_video_by_thirty_data', self.mysql_config(),
                         schema='data-warehouse',
                         if_exists='append',
                         index=False)
        print("最新数据插入成功")

    """粉丝画像接口调用"""
    def fans_refer(self,douyin_unique_id):
        headers = {
            "Cookie":self.get_cookies()
        }
        url = "https://creator.douyin.com/aweme/v1/creator/data/portrait/fans/?aid=2906&app_name=aweme_creator_platform&device_platform=web&referer=https:%2F%2Fcreator.douyin.com%2F&user_agent=Mozilla%2F5.0+(Macintosh%3B+Intel+Mac+OS+X+10_15_7)+AppleWebKit%2F537.36+(KHTML,+like+Gecko)+Chrome%2F128.0.0.0+Safari%2F537.36&cookie_enabled=true&screen_width=1512&screen_height=982&browser_language=zh-CN&browser_platform=MacIntel&browser_name=Mozilla&browser_version=5.0+(Macintosh%3B+Intel+Mac+OS+X+10_15_7)+AppleWebKit%2F537.36+(KHTML,+like+Gecko)+Chrome%2F128.0.0.0+Safari%2F537.36&browser_online=true&timezone_name=Asia%2FShanghai&msToken=HVnrFhmvJZMIHKpgoWCh5U3xzxJSJNbGkinl8GNFYYYq5vzqqgyz__77TEqTmFzARvxJs1GhfD0K71bdLWh5xG4UQTZQgxQN7PjWQEgIwjWrd6-ZtGIBtBzbCv_t8XY=&X-Bogus=DFSzswVY1zTAN9KJtX5tZfLNKBTP&_signature=_02B4Z6wo00001.iJ1SwAAIDAqnb1N9FiCOf4idGAAJjJsTL0G6ZhZj-TKu1LggDeuV1mNVbZIvhdIJLX6cQLXyTAI22sm.o6D.hsemLya10Q3-tKaMBeXYWqtAnDmqL0bOG-p80ygxlG6Rpie8"
        fans_text = requests.get(url, headers=headers)
        fans_text = fans_text.json()
        current_time = datetime.datetime.now()
        user_json = {"douyin_unique_id":douyin_unique_id,"create_time":current_time}
        fans_text.update(user_json)
        print(fans_text)
        # if 'status_msg' in str(fans_text):
        #     print("粉丝画像数据暂未出")
        # 将 active_data 转换为 DataFrame
        df_list = []

        # 遍历数据中的每个列表键，并创建DataFrame
        for key, value in fans_text.items():
            if isinstance(value, list):  # 检查值是否为列表
                temp_df = pd.DataFrame(value)
                temp_df['category'] = key  # 添加一个类别列来标识数据来源
                # print(temp_df)
                df_list.append(temp_df)

            # 合并所有的DataFrame

        print(df_list)
        df = pd.concat(df_list, ignore_index=True)

        # 添加非列表数据作为单独的行（如果需要的话，这里以create_time为例）
        # 注意：这种方式适用于可以将非列表数据表示为单行或多行的情况
        # 如果create_time应该作为单独的一列而不是行，则需要调整数据结构
        # 这里我们假设将其作为一行附加信息添加（可能需要进一步处理以适应实际需求）
        create_time_df = pd.DataFrame(
            [{'category': 'create_time', 'dimension': 'timestamp', 'value': fans_text['create_time'].isoformat()}])
        df = pd.concat([df, create_time_df], ignore_index=True)

        # 显示结果
        df["douyin_account_id"] = douyin_unique_id
        current_time = datetime.datetime.now()

        df["create_time"] = str(current_time)
        delete_sql_trends = "DELETE FROM data_warehouse_douyin_fans_data WHERE douyin_account_id='{}'".format(douyin_unique_id)
        self.pymysql_fetchall(delete_sql_trends)
        pd.io.sql.to_sql(df, 'data_warehouse_douyin_fans_data', self.mysql_config(),
                         schema='data-warehouse',
                         if_exists='append',
                         index=False)


    """抖音创作者中心 粉丝画像模块"""

    def dy_fans_portrait(self,fans_num,douyin_unique_id):
        if int(fans_num)<300:
            print("粉丝不满三百，暂不能获取画像数据")
        else:
            """粉丝画像接口"""
            print("画像数据正在获取中")
            try:

                print(self.fans_refer(douyin_unique_id=douyin_unique_id))
            except:
                print("数据为产出")
        return "粉丝画像计算完成"


    """获取当前时间前90天的数据时间"""

    def get_nine_datetime(self):
        current_time = datetime.datetime.now()
        # 计算往前推90天的时间
        time_90_days_ago = current_time - datetime.timedelta(days=3)
        print(time_90_days_ago)

        return time_90_days_ago
    """作品列表"""
    def work_manager(self,douyin_unique_id):

        delete_sql_trends = "DELETE FROM data_warehouse_douyin_aweme_info_data WHERE douyin_account_id='{}'".format(
            douyin_unique_id)
        self.pymysql_fetchall(delete_sql_trends)

        url = "https://creator.douyin.com/janus/douyin/creator/pc/work_list?scene=star_atlas&device_platform=android&status=0&cookie_enabled=true&screen_width=1512&screen_height=982&browser_language=zh-CN&browser_platform=MacIntel&browser_name=Mozilla&browser_version=5.0+(Macintosh%3B+Intel+Mac+OS+X+10_15_7)+AppleWebKit%2F537.36+(KHTML,+like+Gecko)+Chrome%2F128.0.0.0+Safari%2F537.36&browser_online=true&timezone_name=Asia%2FShanghai&aid=1128"
        headers = {
            "Cookie":self.get_cookies()

        }


        # last_value = None
        data = {"count": 12, "max_cursor": 0}

        while True:

            work_text = requests.get(url, headers=headers, params=data)

            last_value_list = []
            items_list = []
            aweme_list = work_text.json()["aweme_list"]
            aweme_item_list = work_text.json()["items"]

            for i,j in zip(aweme_list,aweme_item_list):
                last_value = i["create_time"]
                    # aweme_url = {"aweme_url":j["cover"]["url_list"][0]}
                import datetime
                current_time = current_time = datetime.datetime.now()

                aweme_id_json = {"douyin_account_id": douyin_unique_id, "aweme_id": i["aweme_id"],"aweme_url":j["cover"]["url_list"][0],"create_time":str(current_time)}
                items_list.append(aweme_id_json)

                last_value_list.append(last_value)

            items_list_aweme_frame = pd.DataFrame(items_list)
            print(items_list_aweme_frame)

            pd.io.sql.to_sql(items_list_aweme_frame, 'data_warehouse_douyin_aweme_info_data', self.mysql_config(),
                                              schema='data-warehouse',
                                              if_exists='append',
                                              index=False)

            """获取每页请求的最后一条数据"""
            from datetime import datetime, timedelta
            try:
                last_value_shijianchuo = str(last_value_list[-1])+'000'
                # print(last_value_shijianchuo)
                data["max_cursor"] = last_value_shijianchuo
                print(data)
                dt_from_timestamp = datetime.fromtimestamp(int(last_value_list[-1]))
                print("从时间戳转换的日期和时间:", dt_from_timestamp)

                time_90_days_ago = self.get_nine_datetime()

                if dt_from_timestamp <= time_90_days_ago:
                    print(dt_from_timestamp)
                    break


            except:
                print("页码分页完毕！")


    """作品详情信息，携带POI"""
    def work_detail_poi(self,cookies,douyin_unique_id):

        headers = {
            "Cookie": cookies
        }
        # data_info_data = pd.read_sql("select * from live_data.data_warehouse_douyin_aweme_info_data where douyin_account_id='{}'".format(douyin_unique_id),self.mysql_config())

        # data_list = [data_info_data.loc[i].to_dict() for i in data_info_data.index.values]


        url = "https://creator.douyin.com/janus/douyin/creator/pc/work_detail?aid=2906&app_name=aweme_creator_platform&device_platform=web&referer=https:%2F%2Fcreator.douyin.com%2Fcreator-micro%2Fcontent%2Fmanage&user_agent=Mozilla%2F5.0+(Macintosh%3B+Intel+Mac+OS+X+10_15_7)+AppleWebKit%2F537.36+(KHTML,+like+Gecko)+Chrome%2F129.0.0.0+Safari%2F537.36&cookie_enabled=true&screen_width=1512&screen_height=982&browser_language=zh-CN&browser_platform=MacIntel&browser_name=Mozilla&browser_version=5.0+(Macintosh%3B+Intel+Mac+OS+X+10_15_7)+AppleWebKit%2F537.36+(KHTML,+like+Gecko)+Chrome%2F129.0.0.0+Safari%2F537.36&browser_online=true&timezone_name=Asia%2FShanghai"
        # time.sleep(1)
        work_detail = requests.get(url,headers=headers)

        work_detail_json = work_detail.json()
        items_list = []

        # # print(work_detail_json)
        try:
            work_items_list_json = work_detail_json["item_list"]
            work_type_json = work_detail_json["items"]

            for j,t in zip(work_items_list_json,work_type_json):
                release_time = j['create_time']
                video_title = j["desc"]
                """***抖音视频发布时间***"""
                items_list_json = {"douyin_video_id":j["aweme_id"]}
                """***获取POI_ID和POI_NAME数据***"""
                if 'poi_info' not in j:
                    print("poi_id为空，poi_name为空")
                    poi_json_not = {"poi_id": "", "poi_name": ""}
                    items_list_json.update(poi_json_not)
                    # items_list.append(items_list_json)

                else:
                    poi_json = {"poi_id":j["poi_info"]["poi_id"],"poi_name":j["poi_info"]["poi_name"]}
                    items_list_json.update(poi_json)
                    # items_list.append(items_list_json)

                """***审核状态***"""
                audit_status = t["type"]
                """***封面地址***"""
                cover_addr = t["cover"]["url_list"][0]
                json_a = {"release_time":release_time,"video_title":video_title,"audit_status": audit_status, "cover_addr": cover_addr,"theme":"","douyin_account_id":douyin_unique_id}
                json_a.update(items_list_json)
                items_list.append(json_a)

        except:
            print("作品为私密作品 访问不到")

        poi_id_name_frame = pd.DataFrame(items_list)
        print(items_list)
        delete_sql_trends = "DELETE FROM data_warehouse_douyin_video_contribute WHERE douyin_account_id='{}'".format(douyin_unique_id)
        self.pymysql_fetchall(delete_sql_trends)
        pd.io.sql.to_sql(poi_id_name_frame, 'data_warehouse_douyin_video_contribute', self.mysql_config(),
                         schema='data-warehouse',
                         if_exists='append',
                         index=False)

        """测试代码"""
        # items_list = []
        # for j in work_items_list_json:
        #     release_time = j['create_time']
        #     video_title = j["desc"]
        #     """***抖音视频发布时间***"""
        #     items_list_json = {"douyin_account_id":douyin_unique_id,"release_time":release_time,"video_title":video_title}
        #     """***获取POI_ID和POI_NAME数据***"""
        #     if 'poi_info' not in j:
        #         print("poi_id为空，poi_name为空")
        #         poi_json_not = {"poi_id": "", "poi_name": ""}
        #         items_list_json.update(poi_json_not)
        #         items_list.append(items_list_json)
        #
        #     else:
        #         poi_json = {"poi_id":j["poi_info"]["poi_id"],"poi_name":j["poi_info"]["poi_name"]}
        #         items_list_json.update(poi_json)
        #         items_list.append(items_list_json)
        #     print(items_list_json)
        #
        #
        # work_type_json = work_detail_json["items"]
        # for ty in work_type_json:
        #     """***审核状态***"""
        #     audit_status = ty["type"]
        #     """***封面地址***"""
        #     cover_addr = ty["cover"]["url_list"][0]
        #     print({"audit_status": audit_status, "cover_addr": cover_addr})


    def work_detail_hour(self,cookies,douyin_unique_id):
        print(douyin_unique_id)
        headers = {
            # "Cookie": "bd_ticket_guard_client_web_domain=2; passport_csrf_token=977a960ef4aa9d0b834baa86d9b58842; passport_csrf_token_default=977a960ef4aa9d0b834baa86d9b58842; ttcid=20cfd75cc9864eb1a0fc4942b37dd83551; s_v_web_id=verify_m18w1lgb_pO620Vb4_cHJk_4wuH_84rw_40gjup8tUVXr; passport_mfa_token=CjXFqvCUDzN0XqIwhxrxUZhyedJhjnPSaOq2qRTLO7mVmAttyrRmSJuVvQtKu1VK3kENFwp6%2BRpKCjx%2F25E%2B6i1CkYs3zReZmeDc9UNG6uNxqWTOcA06H3PxUbYvQ7f%2FmifyHb5O3VnkyOkBX2lnSbTSRlmbyZsQ38HcDRj2sdFsIAIiAQNGaVhf; d_ticket=25c5cbed197e966110d4feebd8d8119c5d194; passport_assist_user=Cjwau2ar_NwRLC2ixe_dhXEsoz57I0-vvhjHeqzg7NGSJb3XgxozLXMU6o6X4ZX-i0w3fSwZVQNc8dKuF3oaSgo8YYC0Rbvz-jb5IOJxnIzJG1_NJ7Wf--FKRoEBxu0HCHsSlN7jKH1Zbo3BncH8ulFtJGlSYTG_nXnV2EUFENLB3A0Yia_WVCABIgEDcI9m4A%3D%3D; n_mh=BLcApAJyDWG0xwZfiZl8GXR4GDSJyJhMmvIui04m6B4; sso_uid_tt=8a4a808434dbc695981896bb660f48a7; sso_uid_tt_ss=8a4a808434dbc695981896bb660f48a7; toutiao_sso_user=21e6ffb53c4c52418cbfd8df8ad99424; toutiao_sso_user_ss=21e6ffb53c4c52418cbfd8df8ad99424; sid_ucp_sso_v1=1.0.0-KDJjNDk0MTRhZTE0OWUxZDZjNjYzZmQzNzkwNzhkMTgzZmY5MWFiNzEKHwjP0NbwvgIQyP6utwYY2hYgDDCZg-HSBTgGQPQHSAYaAmxmIiAyMWU2ZmZiNTNjNGM1MjQxOGNiZmQ4ZGY4YWQ5OTQyNA; ssid_ucp_sso_v1=1.0.0-KDJjNDk0MTRhZTE0OWUxZDZjNjYzZmQzNzkwNzhkMTgzZmY5MWFiNzEKHwjP0NbwvgIQyP6utwYY2hYgDDCZg-HSBTgGQPQHSAYaAmxmIiAyMWU2ZmZiNTNjNGM1MjQxOGNiZmQ4ZGY4YWQ5OTQyNA; passport_auth_status=92a184ef93782587f77f96f0d3e3a4a4%2C; passport_auth_status_ss=92a184ef93782587f77f96f0d3e3a4a4%2C; uid_tt=df03ed6673711ac4872b97f699d2c126; uid_tt_ss=df03ed6673711ac4872b97f699d2c126; sid_tt=00f18b0d75ccbf358fca0db35098911f; sessionid=00f18b0d75ccbf358fca0db35098911f; sessionid_ss=00f18b0d75ccbf358fca0db35098911f; is_staff_user=false; oc_login_type=LOGIN_PERSON; _bd_ticket_crypt_doamin=2; _bd_ticket_crypt_cookie=99b7db74019f20ab7bf1d86e143f2b35; __security_server_data_status=1; sid_guard=00f18b0d75ccbf358fca0db35098911f%7C1726725964%7C5183999%7CMon%2C+18-Nov-2024+06%3A06%3A03+GMT; sid_ucp_v1=1.0.0-KDIxNDQyNWE2MTUxNjllZTE1NWY2OTFmMjE4NGQ5OGI1YmM3ZTU0YzEKGQjP0NbwvgIQzP6utwYY2hYgDDgGQPQHSAQaAmxxIiAwMGYxOGIwZDc1Y2NiZjM1OGZjYTBkYjM1MDk4OTExZg; ssid_ucp_v1=1.0.0-KDIxNDQyNWE2MTUxNjllZTE1NWY2OTFmMjE4NGQ5OGI1YmM3ZTU0YzEKGQjP0NbwvgIQzP6utwYY2hYgDDgGQPQHSAQaAmxxIiAwMGYxOGIwZDc1Y2NiZjM1OGZjYTBkYjM1MDk4OTExZg; _tea_utm_cache_2906=undefined; UIFID_TEMP=63dd93172fb5fdaa8078f33ae0ba1106c8e3f1f870eecbc4b54b497d452c351705d991b09847cfb4e77e5c449a51f49580f9c5a16670964a5380bc79407b9b74366582d28cba029ddf57a51322e5eca6; hevc_supported=true; home_can_add_dy_2_desktop=%220%22; stream_recommend_feed_params=%22%7B%5C%22cookie_enabled%5C%22%3Atrue%2C%5C%22screen_width%5C%22%3A1512%2C%5C%22screen_height%5C%22%3A982%2C%5C%22browser_online%5C%22%3Atrue%2C%5C%22cpu_core_num%5C%22%3A10%2C%5C%22device_memory%5C%22%3A8%2C%5C%22downlink%5C%22%3A10%2C%5C%22effective_type%5C%22%3A%5C%224g%5C%22%2C%5C%22round_trip_time%5C%22%3A150%7D%22; volume_info=%7B%22isUserMute%22%3Afalse%2C%22isMute%22%3Afalse%2C%22volume%22%3A0.5%7D; store-region=cn-zj; store-region-src=uid; odin_tt=a8f584d06370ff960297a6cb064b9eb953f6d5f44874216d54cf89851d2451e465ff49b57cd04e9f5ef43f01ffd4b0f7; csrf_session_id=339011d5e7f539f9baa9fb3c254d7c53; tt_scid=MaocLE5kcYrce7cj1eL-HE6Re1PpGoIFwvWb2IUmJeJ53NcUl.o0g2Fsx6VG.DRt24bd; ttwid=1%7CTRAXmhXVJXjlgpVEpz7ZDIldcnMWzxLd67SkLly3k9o%7C1728566677%7C3d98367cc7d5e8250db3fe9de047b53fa3fc4777ccb9b314c64134bb412901da; bd_ticket_guard_client_data=eyJiZC10aWNrZXQtZ3VhcmQtdmVyc2lvbiI6MiwiYmQtdGlja2V0LWd1YXJkLWl0ZXJhdGlvbi12ZXJzaW9uIjoxLCJiZC10aWNrZXQtZ3VhcmQtcmVlLXB1YmxpYy1rZXkiOiJCSTBZcGY4WDRSejBhWWZGZmh2S1RpRGdSMkR2ak1XbTRqSUZFTUdJeGRzdmh2Q2tONkxVYVlDSGJkcnpVaEFyYlVhUUh0YTZMcmdQY0NDek9XZG8xTjg9IiwiYmQtdGlja2V0LWd1YXJkLXdlYi12ZXJzaW9uIjoyfQ%3D%3D; cc_request_t=6dbd5150-e6e7-489d-9be0-c9745ef79774; msToken=0b3t_pL0qqnx1_OjB9nbqTPqxfv-EIGH7WkHugPLgqpAVNElH_n_Y7yFnWjWs0H4f_vOZGcMs5s5Xm2XhPw8FaL3y1vIlj1ro9gp5ZAi6oMoWBSuwt-lc3IrpGRF; passport_fe_beating_status=true"
            "Cookie":self.get_cookies()

        }
        # for i in data_list:
        # data = {
        #         "user_agent":"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/129.0.0.0 Safari/537.36",
        #         "referer":"https://creator.douyin.com/creator-micro/content/manage",
        #         "item_id":'7421455965414870335',
        #         'index_type_key':'play_count',
        #         "index_data_window":'1',
        #         "index_type":'2'
        #         # "msToken": "frepMuRG-yaM2QaU6arX5FLxpC03Ta8DQaBUcPy3JzolkAt05PS6pO2hU7ic0tXkmZ3u-TnvdaHgHyCJnNrdijdeXHFylPdQ3fm-lY2J8bGNEFnU4wqAM6xwML-J",
        #         # "X-Bogus":"DFSzswVYNbXANj1qtB8sxOIm4LNH",
        #         # "_signature":"_02B4Z6wo00001FHz2IwAAIDBsTgTXhXPa4hR89wAAHOC1o5tuRyt2TBVUS2fM2u3a9q0.MPVQGVcc3jhqMJEnKZ7aARBhi0h0HLuJ22ePUrHBHi4BpnHbtpJjYXQP.EsLpsEmNXYAwqZdJ6.3a"
        # }
        info_data = pd.read_sql("select * from data_warehouse_douyin_aweme_info_data where douyin_account_id='{}'".format(douyin_unique_id),self.mysql_config())
        data_list = [info_data.loc[i].to_dict() for i in info_data.index.values]
        hour_list = []
        t_map_list = []
        for ii in data_list:

            hour_index_type = {"play_count": "1", "play_avg_time": "8", "play_finish_ratio": "7", "digg_count": "3", "comment_count": "4","share_count": "5", "new_fans_count": "9"}
            for key_,index_type in hour_index_type.items():
                # print(index_type,key_)
                data = {
                    'index_type':index_type,
                    'index_type_key':key_,
                    ##时间区间
                    'index_data_window':'1',
                    'item_id':ii['aweme_id']
                }
                url = 'https://creator.douyin.com/web/api/creator/data/item/option/?aid=2906&app_name=aweme_creator_platform&device_platform=web&referer=https:%2F%2Fcreator.douyin.com%2Fcreator-micro%2Fcontent%2Fmanage&user_agent=Mozilla%2F5.0+(Macintosh%3B+Intel+Mac+OS+X+10_15_7)+AppleWebKit%2F537.36+(KHTML,+like+Gecko)+Chrome%2F129.0.0.0+Safari%2F537.36&cookie_enabled=true&screen_width=1512&screen_height=982&browser_language=zh-CN&browser_platform=MacIntel&browser_name=Mozilla&browser_version=5.0+(Macintosh%3B+Intel+Mac+OS+X+10_15_7)+AppleWebKit%2F537.36+(KHTML,+like+Gecko)+Chrome%2F129.0.0.0+Safari%2F537.36&browser_online=true&timezone_name=Asia%2FShanghai&publish_time=1727942387&mcn_type=0&msToken=fMVoKDIG88LQkkhtl2Z2GbcBjXgP6Y3l5rsd6FJQZfqV6CuXPyT_HshGh9aaEBH5CN_WmtBbg00sjNJqbuuWNU0D0II5VqurOkN4YUoIYthyFC7-6yj5np7flZab&X-Bogus=DFSzswVY5EtANj1qtB83QQIm4L7z&_signature=_02B4Z6wo00001wIj5TgAAIDC4ugu6GY3Aj8CI-GAAKeZp63bpOSXZvhVLh6E1A3zWFbmpUBBtHF98pdXyBXNzr.2cEWkWcFcxGteN-zyQizVm8YmqdBcaOMo.VErChNYlBVgLMn47WJObam51e'
                work_detail_hour_get = requests.get(url,headers=headers,params=data)
                work_detail_hour_json = work_detail_hour_get.json()
                for i in work_detail_hour_json["trend_data"]:
                    index_type_json = {"index_type_key":key_,"aweme_id":ii["aweme_id"],"douyin_account_id":douyin_unique_id}
                    # if 'play_count' in index_type_json:
                    # print(index_type_json)
                    i.update(index_type_json)
                    # print(i)
                    if 'douyin_value' in i:
                        # print(i)

                        # 创建一个新字典，用于存储修改后的结果
                        modified_dict = {}

                        # 将 'key' 键的值赋给 'create_time' 键，并添加到新字典中
                        modified_dict['date_time'] = i['key']

                        # 将 'douyin_value' 键添加到新字典中（如果需要的话）
                        modified_dict['douyin_value'] = i['douyin_value']
                        modified_dict['aweme_id'] = i['aweme_id']
                        modified_dict['douyin_account_id'] = i['douyin_account_id']
                        modified_dict['index_type_key'] = i['index_type_key']

                        new_dict = {"douyin_account_id": modified_dict["douyin_account_id"],
                                     key_: modified_dict["douyin_value"],
                                     "aweme_id": modified_dict["aweme_id"],
                                     "date_time": modified_dict["date_time"]}

                        hour_list.append(new_dict)
                    else:
                        modified_dict1 = {}

                        # 将 'key' 键的值赋给 'create_time' 键，并添加到新字典中
                        modified_dict1['date_time'] = i['key']

                        # 将 'douyin_value' 键添加到新字典中（如果需要的话）
                        modified_dict1['value'] = i['value']
                        modified_dict1['aweme_id'] = i['aweme_id']
                        modified_dict1['douyin_account_id'] = i['douyin_account_id']
                        modified_dict1['index_type_key'] = i['index_type_key']
                        # print(modified_dict1)
                        new_dict1 = {"douyin_account_id": modified_dict1["douyin_account_id"], key_: modified_dict1["value"],
                               "aweme_id": modified_dict1["aweme_id"],
                               "date_time": modified_dict1["date_time"]}


                        hour_list.append(new_dict1)
            # url_trend_map = "https://creator.douyin.com/janus/douyin/creator/data/item_analysis/metrics_trend?aid=2906&app_name=aweme_creator_platform&device_platform=web&referer=https:%2F%2Fcreator.douyin.com%2Fcreator-micro%2Fcontent%2Fmanage&user_agent=Mozilla%2F5.0+(Macintosh%3B+Intel+Mac+OS+X+10_15_7)+AppleWebKit%2F537.36+(KHTML,+like+Gecko)+Chrome%2F129.0.0.0+Safari%2F537.36&cookie_enabled=true&screen_width=1512&screen_height=982&browser_language=zh-CN&browser_platform=MacIntel&browser_name=Mozilla&browser_version=5.0+(Macintosh%3B+Intel+Mac+OS+X+10_15_7)+AppleWebKit%2F537.36+(KHTML,+like+Gecko)+Chrome%2F129.0.0.0+Safari%2F537.36&browser_online=true&timezone_name=Asia%2FShanghai&trend_type=2&metrics_group=[0]"
            url_trend_map = "https://creator.douyin.com/janus/douyin/creator/data/item_analysis/metrics_trend?aid=2906&app_name=aweme_creator_platform&device_platform=web&referer=https:%2F%2Fcreator.douyin.com%2Fcreator-micro%2Fcontent%2Fmanage&user_agent=Mozilla%2F5.0+(Macintosh%3B+Intel+Mac+OS+X+10_15_7)+AppleWebKit%2F537.36+(KHTML,+like+Gecko)+Chrome%2F129.0.0.0+Safari%2F537.36&cookie_enabled=true&screen_width=1512&screen_height=982&browser_language=zh-CN&browser_platform=MacIntel&browser_name=Mozilla&browser_version=5.0+(Macintosh%3B+Intel+Mac+OS+X+10_15_7)+AppleWebKit%2F537.36+(KHTML,+like+Gecko)+Chrome%2F129.0.0.0+Safari%2F537.36&browser_online=true&timezone_name=Asia%2FShanghai&trend_type=2&metrics_group=[0]"
            map_list = ["cover_click_rate","favorite_count"]

            # t_map_list = []
            for i in map_list:
                data_map = {
                    "time_unit": "2",
                    "item_id": ii["aweme_id"],
                    "metrics": ["{}".format(i)]
                }

                """封面点击率、弹幕量、收藏量计算"""
                final_data = requests.get(url_trend_map, headers=headers, params=data_map)
                # print(final_data)
                try:
                    for t_map in final_data.json()["trend_map"]["{}".format(i)]["0"]:
                        t_map_json = {i:t_map['value']}
                        # print(t_map_json)
                        t_map_list.append(t_map_json)

                except:
                    t_map_json = {i:''}
                    t_map_list.append(t_map_json)

            # print(t_map_list)

        hour_frame = pd.DataFrame(hour_list)
        t_map_frame = pd.DataFrame(t_map_list)
        total_frame = pd.concat([hour_frame,t_map_frame],axis=1)

        total_frame.rename(columns={"play_avg_time":"ave_play_count","play_finish_ratio":"completion_rate","digg_count":"like_count","new_fans_count":"Increase_followers_count"},inplace=True)

        delete_sql_trends = "DELETE FROM data_warehouse_douyin_video_contribute_by_hour_data WHERE douyin_account_id='{}'".format(douyin_unique_id)
        self.pymysql_fetchall(delete_sql_trends)
        pd.io.sql.to_sql(total_frame, 'data_warehouse_douyin_video_contribute_by_hour_data', self.mysql_config(),
                         schema='data-warehouse',
                         if_exists='append',
                         index=False)

    def by_day_data_default(self,douyin_unique_id):
        douyin_value_frame = pd.read_sql("select * from by_day_test_1 where douyin_account_id='{}'".format(douyin_unique_id), self.mysql_config())
        # douyin_value_frame = douyin_value_frame.drop(["value", "xigua_value"], axis=1)
        data_list = [douyin_value_frame.loc[i].to_dict() for i in douyin_value_frame.index.values]
        play_count = []
        play_avg_time = []
        digg_count = []
        comment_count = []
        share_count = []
        new_fans_count = []
        play_finish_ratio_count = []
        for i in data_list:

            if i['index_type_key'] == "play_count":
                i_json = {"play_count": i["douyin_value"], "datetime": i["key"], "aweme_id": i["aweme_id"],
                          "douyin_account_id": i["douyin_account_id"]}
                play_count.append(i_json)

            if i['index_type_key'] == "play_avg_time":
                i_json = {"play_avg_time": i["douyin_value"]}

                play_avg_time.append(i_json)

            if i['index_type_key'] == "digg_count":
                i_json = {"digg_count": i["douyin_value"]}

                digg_count.append(i_json)

            if i['index_type_key'] == "comment_count":
                i_json = {"comment_count": i["douyin_value"]}
                comment_count.append(i_json)

            if i['index_type_key'] == "share_count":
                i_json = {"share_count": i["douyin_value"]}
                share_count.append(i_json)

            if i['index_type_key'] == "new_fans_count":
                i_json = {"new_fans_count": i["douyin_value"]}
                new_fans_count.append(i_json)

            if i['index_type_key'] == "play_finish_ratio":
                i_json = {"play_finish_ratio": i["douyin_value"]}
                play_finish_ratio_count.append(i_json)
        play_count_frame = pd.DataFrame(play_count)
        play_avg_time_frame = pd.DataFrame(play_avg_time)
        digg_count = pd.DataFrame(digg_count)
        comment_count = pd.DataFrame(comment_count)
        share_count = pd.DataFrame(share_count)
        new_fans_count = pd.DataFrame(new_fans_count)
        play_finish_ratio_frame = pd.DataFrame(play_finish_ratio_count)
        cc = pd.concat([play_avg_time_frame, play_count_frame, digg_count, comment_count, share_count, new_fans_count,play_finish_ratio_frame],
                       axis=1)
        cc.rename(columns={"play_avg_time":"ave_play_count","play_finish_ratio":"completion_rate","digg_count":"like_count","new_fans_count":"Increase_followers_count","datetime":"date_time"},inplace=True)
        cc["favorite_count"] = 0
        cc["danmaku_count"] = 0
        cc["cover_click_rate"] = 0
        delete_sql_trends = "DELETE FROM data_warehouse_douyin_video_contribute_by_day_data WHERE douyin_account_id='{}'".format(
            douyin_unique_id)
        self.pymysql_fetchall(delete_sql_trends)
        pd.io.sql.to_sql(cc, 'data_warehouse_douyin_video_contribute_by_day_data',
                         self.mysql_config(),
                         schema='data-warehouse',
                         if_exists='append',
                         index=False)
        return cc

    def by_day_data_default_1(self,douyin_unique_id):
        douyin_value_frame = pd.read_sql("select * from by_day_test_2 where douyin_account_id='{}'".format(douyin_unique_id), self.mysql_config())
        # douyin_value_frame = douyin_value_frame.drop(["value", "xigua_value"], axis=1)
        data_list = [douyin_value_frame.loc[i].to_dict() for i in douyin_value_frame.index.values]
        play_count = []
        play_avg_time = []
        digg_count = []
        comment_count = []
        share_count = []
        new_fans_count = []
        play_finish_ratio_count = []
        for i in data_list:

            if i['index_type_key'] == "play_count":
                i_json = {"play_count": i["douyin_value"], "datetime": i["key"], "aweme_id": i["aweme_id"],
                          "douyin_account_id": i["douyin_account_id"]}
                play_count.append(i_json)

            if i['index_type_key'] == "play_avg_time":
                i_json = {"play_avg_time": i["douyin_value"]}

                play_avg_time.append(i_json)

            if i['index_type_key'] == "digg_count":
                i_json = {"digg_count": i["douyin_value"]}

                digg_count.append(i_json)

            if i['index_type_key'] == "comment_count":
                i_json = {"comment_count": i["douyin_value"]}
                comment_count.append(i_json)

            if i['index_type_key'] == "share_count":
                i_json = {"share_count": i["douyin_value"]}
                share_count.append(i_json)

            if i['index_type_key'] == "new_fans_count":
                i_json = {"new_fans_count": i["douyin_value"]}
                new_fans_count.append(i_json)

            if i['index_type_key'] == "play_finish_ratio":
                i_json = {"play_finish_ratio": i["douyin_value"]}
                play_finish_ratio_count.append(i_json)
        play_count_frame = pd.DataFrame(play_count)
        play_avg_time_frame = pd.DataFrame(play_avg_time)
        digg_count = pd.DataFrame(digg_count)
        comment_count = pd.DataFrame(comment_count)
        share_count = pd.DataFrame(share_count)
        new_fans_count = pd.DataFrame(new_fans_count)
        play_finish_ratio_frame = pd.DataFrame(play_finish_ratio_count)
        cc = pd.concat([play_avg_time_frame, play_count_frame, digg_count, comment_count, share_count, new_fans_count,play_finish_ratio_frame],
                       axis=1)
        cc.rename(columns={"play_avg_time": "ave_play_count", "play_finish_ratio": "completion_rate",
                           "digg_count": "like_count", "new_fans_count": "Increase_followers_count",
                           "datetime": "date_time"}, inplace=True)
        cc["favorite_count"] = 0
        cc["cover_click_rate"] = 0
        delete_sql_trends = "DELETE FROM data_warehouse_douyin_video_contribute_by_hour_data WHERE douyin_account_id='{}'".format(
            douyin_unique_id)
        self.pymysql_fetchall(delete_sql_trends)
        pd.io.sql.to_sql(cc, 'data_warehouse_douyin_video_contribute_by_hour_data',
                         self.mysql_config(),
                         schema='data-warehouse',
                         if_exists='append',
                         index=False)
        return cc

    def by_day_data_default_finish(self):
        douyin_value_frame = pd.read_sql("select * from by_day_test_2", self.mysql_config())
        data_list = [douyin_value_frame.loc[i].to_dict() for i in douyin_value_frame.index.values]
        play_finish_ratio = []

        for i in data_list:

            if i['index_type_key'] == "play_finish_ratio":
                i_json = {"play_finish_ratio": i["value"], "aweme_id": i["aweme_id"],
                          "douyin_account_id": i["douyin_account_id"]}
                play_finish_ratio.append(i_json)

        play_finish_ratio_frame = pd.DataFrame(play_finish_ratio)

        # print(play_finish_ratio_frame)
        return play_finish_ratio_frame

    def work_detail_by_day_play_count(self,douyin_unique_id,type_time):
        # by_day_test_1_delete = "DELETE FROM by_day_test_1 WHERE douyin_account_id='{}'".format(douyin_unique_id)
        # self.pymysql_fetchall(by_day_test_1_delete)
        # by_day_test_2_delete = "DELETE FROM by_day_test_2 WHERE douyin_account_id='{}'".format(douyin_unique_id)
        # self.pymysql_fetchall(by_day_test_2_delete)
        print(douyin_unique_id)
        headers = {
            # "Cookie": "bd_ticket_guard_client_web_domain=2; passport_csrf_token=977a960ef4aa9d0b834baa86d9b58842; passport_csrf_token_default=977a960ef4aa9d0b834baa86d9b58842; ttcid=20cfd75cc9864eb1a0fc4942b37dd83551; s_v_web_id=verify_m18w1lgb_pO620Vb4_cHJk_4wuH_84rw_40gjup8tUVXr; passport_mfa_token=CjXFqvCUDzN0XqIwhxrxUZhyedJhjnPSaOq2qRTLO7mVmAttyrRmSJuVvQtKu1VK3kENFwp6%2BRpKCjx%2F25E%2B6i1CkYs3zReZmeDc9UNG6uNxqWTOcA06H3PxUbYvQ7f%2FmifyHb5O3VnkyOkBX2lnSbTSRlmbyZsQ38HcDRj2sdFsIAIiAQNGaVhf; d_ticket=25c5cbed197e966110d4feebd8d8119c5d194; passport_assist_user=Cjwau2ar_NwRLC2ixe_dhXEsoz57I0-vvhjHeqzg7NGSJb3XgxozLXMU6o6X4ZX-i0w3fSwZVQNc8dKuF3oaSgo8YYC0Rbvz-jb5IOJxnIzJG1_NJ7Wf--FKRoEBxu0HCHsSlN7jKH1Zbo3BncH8ulFtJGlSYTG_nXnV2EUFENLB3A0Yia_WVCABIgEDcI9m4A%3D%3D; n_mh=BLcApAJyDWG0xwZfiZl8GXR4GDSJyJhMmvIui04m6B4; sso_uid_tt=8a4a808434dbc695981896bb660f48a7; sso_uid_tt_ss=8a4a808434dbc695981896bb660f48a7; toutiao_sso_user=21e6ffb53c4c52418cbfd8df8ad99424; toutiao_sso_user_ss=21e6ffb53c4c52418cbfd8df8ad99424; sid_ucp_sso_v1=1.0.0-KDJjNDk0MTRhZTE0OWUxZDZjNjYzZmQzNzkwNzhkMTgzZmY5MWFiNzEKHwjP0NbwvgIQyP6utwYY2hYgDDCZg-HSBTgGQPQHSAYaAmxmIiAyMWU2ZmZiNTNjNGM1MjQxOGNiZmQ4ZGY4YWQ5OTQyNA; ssid_ucp_sso_v1=1.0.0-KDJjNDk0MTRhZTE0OWUxZDZjNjYzZmQzNzkwNzhkMTgzZmY5MWFiNzEKHwjP0NbwvgIQyP6utwYY2hYgDDCZg-HSBTgGQPQHSAYaAmxmIiAyMWU2ZmZiNTNjNGM1MjQxOGNiZmQ4ZGY4YWQ5OTQyNA; passport_auth_status=92a184ef93782587f77f96f0d3e3a4a4%2C; passport_auth_status_ss=92a184ef93782587f77f96f0d3e3a4a4%2C; uid_tt=df03ed6673711ac4872b97f699d2c126; uid_tt_ss=df03ed6673711ac4872b97f699d2c126; sid_tt=00f18b0d75ccbf358fca0db35098911f; sessionid=00f18b0d75ccbf358fca0db35098911f; sessionid_ss=00f18b0d75ccbf358fca0db35098911f; is_staff_user=false; oc_login_type=LOGIN_PERSON; _bd_ticket_crypt_doamin=2; _bd_ticket_crypt_cookie=99b7db74019f20ab7bf1d86e143f2b35; __security_server_data_status=1; sid_guard=00f18b0d75ccbf358fca0db35098911f%7C1726725964%7C5183999%7CMon%2C+18-Nov-2024+06%3A06%3A03+GMT; sid_ucp_v1=1.0.0-KDIxNDQyNWE2MTUxNjllZTE1NWY2OTFmMjE4NGQ5OGI1YmM3ZTU0YzEKGQjP0NbwvgIQzP6utwYY2hYgDDgGQPQHSAQaAmxxIiAwMGYxOGIwZDc1Y2NiZjM1OGZjYTBkYjM1MDk4OTExZg; ssid_ucp_v1=1.0.0-KDIxNDQyNWE2MTUxNjllZTE1NWY2OTFmMjE4NGQ5OGI1YmM3ZTU0YzEKGQjP0NbwvgIQzP6utwYY2hYgDDgGQPQHSAQaAmxxIiAwMGYxOGIwZDc1Y2NiZjM1OGZjYTBkYjM1MDk4OTExZg; _tea_utm_cache_2906=undefined; UIFID_TEMP=63dd93172fb5fdaa8078f33ae0ba1106c8e3f1f870eecbc4b54b497d452c351705d991b09847cfb4e77e5c449a51f49580f9c5a16670964a5380bc79407b9b74366582d28cba029ddf57a51322e5eca6; hevc_supported=true; home_can_add_dy_2_desktop=%220%22; stream_recommend_feed_params=%22%7B%5C%22cookie_enabled%5C%22%3Atrue%2C%5C%22screen_width%5C%22%3A1512%2C%5C%22screen_height%5C%22%3A982%2C%5C%22browser_online%5C%22%3Atrue%2C%5C%22cpu_core_num%5C%22%3A10%2C%5C%22device_memory%5C%22%3A8%2C%5C%22downlink%5C%22%3A10%2C%5C%22effective_type%5C%22%3A%5C%224g%5C%22%2C%5C%22round_trip_time%5C%22%3A150%7D%22; volume_info=%7B%22isUserMute%22%3Afalse%2C%22isMute%22%3Afalse%2C%22volume%22%3A0.5%7D; store-region=cn-zj; store-region-src=uid; odin_tt=a8f584d06370ff960297a6cb064b9eb953f6d5f44874216d54cf89851d2451e465ff49b57cd04e9f5ef43f01ffd4b0f7; csrf_session_id=339011d5e7f539f9baa9fb3c254d7c53; tt_scid=MaocLE5kcYrce7cj1eL-HE6Re1PpGoIFwvWb2IUmJeJ53NcUl.o0g2Fsx6VG.DRt24bd; ttwid=1%7CTRAXmhXVJXjlgpVEpz7ZDIldcnMWzxLd67SkLly3k9o%7C1728566677%7C3d98367cc7d5e8250db3fe9de047b53fa3fc4777ccb9b314c64134bb412901da; bd_ticket_guard_client_data=eyJiZC10aWNrZXQtZ3VhcmQtdmVyc2lvbiI6MiwiYmQtdGlja2V0LWd1YXJkLWl0ZXJhdGlvbi12ZXJzaW9uIjoxLCJiZC10aWNrZXQtZ3VhcmQtcmVlLXB1YmxpYy1rZXkiOiJCSTBZcGY4WDRSejBhWWZGZmh2S1RpRGdSMkR2ak1XbTRqSUZFTUdJeGRzdmh2Q2tONkxVYVlDSGJkcnpVaEFyYlVhUUh0YTZMcmdQY0NDek9XZG8xTjg9IiwiYmQtdGlja2V0LWd1YXJkLXdlYi12ZXJzaW9uIjoyfQ%3D%3D; cc_request_t=6dbd5150-e6e7-489d-9be0-c9745ef79774; msToken=0b3t_pL0qqnx1_OjB9nbqTPqxfv-EIGH7WkHugPLgqpAVNElH_n_Y7yFnWjWs0H4f_vOZGcMs5s5Xm2XhPw8FaL3y1vIlj1ro9gp5ZAi6oMoWBSuwt-lc3IrpGRF; passport_fe_beating_status=true"
            "Cookie":self.get_cookies()

        }

        info_data = pd.read_sql("select * from data_warehouse_douyin_aweme_info_data where douyin_account_id='{}'".format(douyin_unique_id),self.mysql_config())
        data_list = [info_data.loc[i].to_dict() for i in info_data.index.values]
        t_map_list = []


        for ii in data_list:

            # hour_index_type = {"play_count": "1", "play_avg_time": "8", "play_finish_ratio": "7", "digg_count": "3", "comment_count": "4","share_count": "5", "new_fans_count": "9"}
            # for key_,index_type in hour_index_type.items():
            play_count = {
                'index_type':'1',
                'index_type_key':'play_count',
                ##时间区间
                'index_data_window':type_time,
                'item_id':ii['aweme_id']
            }
            url = 'https://creator.douyin.com/web/api/creator/data/item/option/?aid=2906&app_name=aweme_creator_platform&device_platform=web&referer=https:%2F%2Fcreator.douyin.com%2Fcreator-micro%2Fcontent%2Fmanage&user_agent=Mozilla%2F5.0+(Macintosh%3B+Intel+Mac+OS+X+10_15_7)+AppleWebKit%2F537.36+(KHTML,+like+Gecko)+Chrome%2F129.0.0.0+Safari%2F537.36&cookie_enabled=true&screen_width=1512&screen_height=982&browser_language=zh-CN&browser_platform=MacIntel&browser_name=Mozilla&browser_version=5.0+(Macintosh%3B+Intel+Mac+OS+X+10_15_7)+AppleWebKit%2F537.36+(KHTML,+like+Gecko)+Chrome%2F129.0.0.0+Safari%2F537.36&browser_online=true&timezone_name=Asia%2FShanghai&publish_time=1727942387&mcn_type=0&msToken=fMVoKDIG88LQkkhtl2Z2GbcBjXgP6Y3l5rsd6FJQZfqV6CuXPyT_HshGh9aaEBH5CN_WmtBbg00sjNJqbuuWNU0D0II5VqurOkN4YUoIYthyFC7-6yj5np7flZab&X-Bogus=DFSzswVY5EtANj1qtB83QQIm4L7z&_signature=_02B4Z6wo00001wIj5TgAAIDC4ugu6GY3Aj8CI-GAAKeZp63bpOSXZvhVLh6E1A3zWFbmpUBBtHF98pdXyBXNzr.2cEWkWcFcxGteN-zyQizVm8YmqdBcaOMo.VErChNYlBVgLMn47WJObam51e'
            work_detail_hour_get = requests.get(url,headers=headers,params=play_count)
            work_detail_hour_json = work_detail_hour_get.json()
            # print(work_detail_hour_json)
            play_count_json = {"play_count":work_detail_hour_json['trend_data'],"aweme_id":ii["aweme_id"],"douyin_account_id": douyin_unique_id}
            t_map_list.append(play_count_json)
        total_frame = pd.DataFrame(t_map_list)
        total_frame = total_frame.astype("str")
        return total_frame
            # pd.io.sql.to_sql(total_frame, 'play_count',
            #                  self.mysql_config(),
            #                  schema='data-warehouse',
            #                  if_exists='append',
            #                  index=False)





        #         for i in work_detail_hour_json["trend_data"]:
        #             index_type_json = {"index_type_key": key_, "aweme_id": ii["aweme_id"],
        #                                "douyin_account_id": douyin_unique_id}
        #
        #             i.update(index_type_json)
        #
        #             if 'douyin_value' in i:
        #
        #                 del i["value"]
        #                 del i["xigua_value"]
        #
        #                 # by_day_result_json ={i["index_type_key"]:i["douyin_value"],"date_time":i["key"],"aweme_id":i['aweme_id'],"douyin_account_id":i["douyin_account_id"]}
        #
        #                 dataframe = pd.DataFrame([i])
        #
        #                 pd.io.sql.to_sql(dataframe, 'by_day_test_1',
        #                                          self.mysql_config(),
        #                                          schema='data-warehouse',
        #                                          if_exists='append',
        #                                          index=False)
        #
        #             else:
        #                 # i.update(index_type_json)
        #                 dataframe1 = pd.DataFrame([i])
        #
        #                 pd.io.sql.to_sql(dataframe1, 'by_day_test_2',
        #                                  self.mysql_config(),
        #                                  schema='data-warehouse',
        #                                  if_exists='append',
        #                                  index=False)
        #
        #     # url_trend_map = "https://creator.douyin.com/janus/douyin/creator/data/item_analysis/metrics_trend?aid=2906&app_name=aweme_creator_platform&device_platform=web&referer=https:%2F%2Fcreator.douyin.com%2Fcreator-micro%2Fcontent%2Fmanage&user_agent=Mozilla%2F5.0+(Macintosh%3B+Intel+Mac+OS+X+10_15_7)+AppleWebKit%2F537.36+(KHTML,+like+Gecko)+Chrome%2F129.0.0.0+Safari%2F537.36&cookie_enabled=true&screen_width=1512&screen_height=982&browser_language=zh-CN&browser_platform=MacIntel&browser_name=Mozilla&browser_version=5.0+(Macintosh%3B+Intel+Mac+OS+X+10_15_7)+AppleWebKit%2F537.36+(KHTML,+like+Gecko)+Chrome%2F129.0.0.0+Safari%2F537.36&browser_online=true&timezone_name=Asia%2FShanghai&trend_type=2&metrics_group=[0]"
        #     url_trend_map = "https://creator.douyin.com/janus/douyin/creator/data/item_analysis/metrics_trend?aid=2906&app_name=aweme_creator_platform&device_platform=web&referer=https:%2F%2Fcreator.douyin.com%2Fcreator-micro%2Fcontent%2Fmanage&user_agent=Mozilla%2F5.0+(Macintosh%3B+Intel+Mac+OS+X+10_15_7)+AppleWebKit%2F537.36+(KHTML,+like+Gecko)+Chrome%2F129.0.0.0+Safari%2F537.36&cookie_enabled=true&screen_width=1512&screen_height=982&browser_language=zh-CN&browser_platform=MacIntel&browser_name=Mozilla&browser_version=5.0+(Macintosh%3B+Intel+Mac+OS+X+10_15_7)+AppleWebKit%2F537.36+(KHTML,+like+Gecko)+Chrome%2F129.0.0.0+Safari%2F537.36&browser_online=true&timezone_name=Asia%2FShanghai&trend_type=2&metrics_group=[0]"
        #     map_list = ["cover_click_rate","favorite_count","danmaku_count"]
        #
        #     # t_map_list = []
        #     for i in map_list:
        #         data_map = {
        #             "time_unit": "1",
        #             "item_id": ii["aweme_id"],
        #             "metrics": ["{}".format(i)]
        #         }
        #
        #         """封面点击率、弹幕量、收藏量计算"""
        #         final_data = requests.get(url_trend_map, headers=headers, params=data_map)
        #         # print(final_data)
        #         try:
        #             for t_map in final_data.json()["trend_map"]["{}".format(i)]["0"]:
        #                 t_map_json = {i:t_map['value']}
        #                 # print(t_map_json)
        #                 t_map_list.append(t_map_json)
        #
        #         except:
        #             t_map_json = {i:''}
        #             t_map_list.append(t_map_json)
        #
        # oneframe = self.by_day_data_default()
        # twoframe = self.by_day_data_default_finish()
        # t_map_frame = pd.DataFrame(t_map_list)
        # print(t_map_frame)
        # total_frame = pd.concat([oneframe,twoframe,t_map_frame],axis=1)
        #
        # total_frame.rename(columns={"douyin_video_id":'aweme_id',"play_avg_time":"ave_play_count","play_finish_ratio":"completion_rate","digg_count":"like_count","new_fans_count":"Increase_followers_count"},inplace=True)
        # # # total_frame = total_frame.drop(["status"],axis=1)
        # print(total_frame.columns)
        # # delete_sql_trends = "DELETE FROM data_warehouse_douyin_video_contribute_by_day_data WHERE douyin_account_id='{}'".format(douyin_unique_id)
        # # self.pymysql_fetchall(delete_sql_trends)
        # # print("走到这里了**********************************")
        # # print(total_frame.columns)
        # pd.io.sql.to_sql(total_frame, 'data_warehouse_douyin_video_contribute_by_day_data_test', self.mysql_config(),
        #                  schema='data-warehouse',
        #                  if_exists='append',
        #                  index=False)
        # print("入库成功")

    def work_detail_play_avg_time(self,douyin_unique_id,type_time):

        headers = {
            # "Cookie": "bd_ticket_guard_client_web_domain=2; passport_csrf_token=977a960ef4aa9d0b834baa86d9b58842; passport_csrf_token_default=977a960ef4aa9d0b834baa86d9b58842; ttcid=20cfd75cc9864eb1a0fc4942b37dd83551; s_v_web_id=verify_m18w1lgb_pO620Vb4_cHJk_4wuH_84rw_40gjup8tUVXr; passport_mfa_token=CjXFqvCUDzN0XqIwhxrxUZhyedJhjnPSaOq2qRTLO7mVmAttyrRmSJuVvQtKu1VK3kENFwp6%2BRpKCjx%2F25E%2B6i1CkYs3zReZmeDc9UNG6uNxqWTOcA06H3PxUbYvQ7f%2FmifyHb5O3VnkyOkBX2lnSbTSRlmbyZsQ38HcDRj2sdFsIAIiAQNGaVhf; d_ticket=25c5cbed197e966110d4feebd8d8119c5d194; passport_assist_user=Cjwau2ar_NwRLC2ixe_dhXEsoz57I0-vvhjHeqzg7NGSJb3XgxozLXMU6o6X4ZX-i0w3fSwZVQNc8dKuF3oaSgo8YYC0Rbvz-jb5IOJxnIzJG1_NJ7Wf--FKRoEBxu0HCHsSlN7jKH1Zbo3BncH8ulFtJGlSYTG_nXnV2EUFENLB3A0Yia_WVCABIgEDcI9m4A%3D%3D; n_mh=BLcApAJyDWG0xwZfiZl8GXR4GDSJyJhMmvIui04m6B4; sso_uid_tt=8a4a808434dbc695981896bb660f48a7; sso_uid_tt_ss=8a4a808434dbc695981896bb660f48a7; toutiao_sso_user=21e6ffb53c4c52418cbfd8df8ad99424; toutiao_sso_user_ss=21e6ffb53c4c52418cbfd8df8ad99424; sid_ucp_sso_v1=1.0.0-KDJjNDk0MTRhZTE0OWUxZDZjNjYzZmQzNzkwNzhkMTgzZmY5MWFiNzEKHwjP0NbwvgIQyP6utwYY2hYgDDCZg-HSBTgGQPQHSAYaAmxmIiAyMWU2ZmZiNTNjNGM1MjQxOGNiZmQ4ZGY4YWQ5OTQyNA; ssid_ucp_sso_v1=1.0.0-KDJjNDk0MTRhZTE0OWUxZDZjNjYzZmQzNzkwNzhkMTgzZmY5MWFiNzEKHwjP0NbwvgIQyP6utwYY2hYgDDCZg-HSBTgGQPQHSAYaAmxmIiAyMWU2ZmZiNTNjNGM1MjQxOGNiZmQ4ZGY4YWQ5OTQyNA; passport_auth_status=92a184ef93782587f77f96f0d3e3a4a4%2C; passport_auth_status_ss=92a184ef93782587f77f96f0d3e3a4a4%2C; uid_tt=df03ed6673711ac4872b97f699d2c126; uid_tt_ss=df03ed6673711ac4872b97f699d2c126; sid_tt=00f18b0d75ccbf358fca0db35098911f; sessionid=00f18b0d75ccbf358fca0db35098911f; sessionid_ss=00f18b0d75ccbf358fca0db35098911f; is_staff_user=false; oc_login_type=LOGIN_PERSON; _bd_ticket_crypt_doamin=2; _bd_ticket_crypt_cookie=99b7db74019f20ab7bf1d86e143f2b35; __security_server_data_status=1; sid_guard=00f18b0d75ccbf358fca0db35098911f%7C1726725964%7C5183999%7CMon%2C+18-Nov-2024+06%3A06%3A03+GMT; sid_ucp_v1=1.0.0-KDIxNDQyNWE2MTUxNjllZTE1NWY2OTFmMjE4NGQ5OGI1YmM3ZTU0YzEKGQjP0NbwvgIQzP6utwYY2hYgDDgGQPQHSAQaAmxxIiAwMGYxOGIwZDc1Y2NiZjM1OGZjYTBkYjM1MDk4OTExZg; ssid_ucp_v1=1.0.0-KDIxNDQyNWE2MTUxNjllZTE1NWY2OTFmMjE4NGQ5OGI1YmM3ZTU0YzEKGQjP0NbwvgIQzP6utwYY2hYgDDgGQPQHSAQaAmxxIiAwMGYxOGIwZDc1Y2NiZjM1OGZjYTBkYjM1MDk4OTExZg; _tea_utm_cache_2906=undefined; UIFID_TEMP=63dd93172fb5fdaa8078f33ae0ba1106c8e3f1f870eecbc4b54b497d452c351705d991b09847cfb4e77e5c449a51f49580f9c5a16670964a5380bc79407b9b74366582d28cba029ddf57a51322e5eca6; hevc_supported=true; home_can_add_dy_2_desktop=%220%22; stream_recommend_feed_params=%22%7B%5C%22cookie_enabled%5C%22%3Atrue%2C%5C%22screen_width%5C%22%3A1512%2C%5C%22screen_height%5C%22%3A982%2C%5C%22browser_online%5C%22%3Atrue%2C%5C%22cpu_core_num%5C%22%3A10%2C%5C%22device_memory%5C%22%3A8%2C%5C%22downlink%5C%22%3A10%2C%5C%22effective_type%5C%22%3A%5C%224g%5C%22%2C%5C%22round_trip_time%5C%22%3A150%7D%22; volume_info=%7B%22isUserMute%22%3Afalse%2C%22isMute%22%3Afalse%2C%22volume%22%3A0.5%7D; store-region=cn-zj; store-region-src=uid; odin_tt=a8f584d06370ff960297a6cb064b9eb953f6d5f44874216d54cf89851d2451e465ff49b57cd04e9f5ef43f01ffd4b0f7; csrf_session_id=339011d5e7f539f9baa9fb3c254d7c53; tt_scid=MaocLE5kcYrce7cj1eL-HE6Re1PpGoIFwvWb2IUmJeJ53NcUl.o0g2Fsx6VG.DRt24bd; ttwid=1%7CTRAXmhXVJXjlgpVEpz7ZDIldcnMWzxLd67SkLly3k9o%7C1728566677%7C3d98367cc7d5e8250db3fe9de047b53fa3fc4777ccb9b314c64134bb412901da; bd_ticket_guard_client_data=eyJiZC10aWNrZXQtZ3VhcmQtdmVyc2lvbiI6MiwiYmQtdGlja2V0LWd1YXJkLWl0ZXJhdGlvbi12ZXJzaW9uIjoxLCJiZC10aWNrZXQtZ3VhcmQtcmVlLXB1YmxpYy1rZXkiOiJCSTBZcGY4WDRSejBhWWZGZmh2S1RpRGdSMkR2ak1XbTRqSUZFTUdJeGRzdmh2Q2tONkxVYVlDSGJkcnpVaEFyYlVhUUh0YTZMcmdQY0NDek9XZG8xTjg9IiwiYmQtdGlja2V0LWd1YXJkLXdlYi12ZXJzaW9uIjoyfQ%3D%3D; cc_request_t=6dbd5150-e6e7-489d-9be0-c9745ef79774; msToken=0b3t_pL0qqnx1_OjB9nbqTPqxfv-EIGH7WkHugPLgqpAVNElH_n_Y7yFnWjWs0H4f_vOZGcMs5s5Xm2XhPw8FaL3y1vIlj1ro9gp5ZAi6oMoWBSuwt-lc3IrpGRF; passport_fe_beating_status=true"
            "Cookie": self.get_cookies()

        }

        info_data = pd.read_sql(
            "select * from data_warehouse_douyin_aweme_info_data where douyin_account_id='{}'".format(douyin_unique_id),
            self.mysql_config())
        data_list = [info_data.loc[i].to_dict() for i in info_data.index.values]

        play_avg_time_list = []
        for ii in data_list:
            play_avg_time = {
                'index_type': '8',
                'index_type_key': 'play_avg_time',
                ##时间区间
                'index_data_window': type_time,
                'item_id': ii['aweme_id']
            }
            play_avg_time_url = 'https://creator.douyin.com/web/api/creator/data/item/option/?aid=2906&app_name=aweme_creator_platform&device_platform=web&referer=https:%2F%2Fcreator.douyin.com%2Fcreator-micro%2Fcontent%2Fmanage&user_agent=Mozilla%2F5.0+(Macintosh%3B+Intel+Mac+OS+X+10_15_7)+AppleWebKit%2F537.36+(KHTML,+like+Gecko)+Chrome%2F129.0.0.0+Safari%2F537.36&cookie_enabled=true&screen_width=1512&screen_height=982&browser_language=zh-CN&browser_platform=MacIntel&browser_name=Mozilla&browser_version=5.0+(Macintosh%3B+Intel+Mac+OS+X+10_15_7)+AppleWebKit%2F537.36+(KHTML,+like+Gecko)+Chrome%2F129.0.0.0+Safari%2F537.36&browser_online=true&timezone_name=Asia%2FShanghai&publish_time=1727942387&mcn_type=0&msToken=fMVoKDIG88LQkkhtl2Z2GbcBjXgP6Y3l5rsd6FJQZfqV6CuXPyT_HshGh9aaEBH5CN_WmtBbg00sjNJqbuuWNU0D0II5VqurOkN4YUoIYthyFC7-6yj5np7flZab&X-Bogus=DFSzswVY5EtANj1qtB83QQIm4L7z&_signature=_02B4Z6wo00001wIj5TgAAIDC4ugu6GY3Aj8CI-GAAKeZp63bpOSXZvhVLh6E1A3zWFbmpUBBtHF98pdXyBXNzr.2cEWkWcFcxGteN-zyQizVm8YmqdBcaOMo.VErChNYlBVgLMn47WJObam51e'
            play_avg_time_get = requests.get(play_avg_time_url, headers=headers, params=play_avg_time)
            play_avg_time_get = play_avg_time_get.json()
            # print(work_detail_hour_json)
            play_avg_time_list.append({"play_avg_time": play_avg_time_get['trend_data']})
        play_avg_time_frame = pd.DataFrame(play_avg_time_list)
        play_avg_time_frame = play_avg_time_frame.astype("str")
        return play_avg_time_frame
            # pd.io.sql.to_sql(play_avg_time_frame, 'play_avg_time',
            #                  self.mysql_config(),
            #                  schema='data-warehouse',
            #                  if_exists='append',
            #                  index=False)

    def work_detail_play_finish_ratio(self,douyin_unique_id,type_time):

        headers = {
            # "Cookie": "bd_ticket_guard_client_web_domain=2; passport_csrf_token=977a960ef4aa9d0b834baa86d9b58842; passport_csrf_token_default=977a960ef4aa9d0b834baa86d9b58842; ttcid=20cfd75cc9864eb1a0fc4942b37dd83551; s_v_web_id=verify_m18w1lgb_pO620Vb4_cHJk_4wuH_84rw_40gjup8tUVXr; passport_mfa_token=CjXFqvCUDzN0XqIwhxrxUZhyedJhjnPSaOq2qRTLO7mVmAttyrRmSJuVvQtKu1VK3kENFwp6%2BRpKCjx%2F25E%2B6i1CkYs3zReZmeDc9UNG6uNxqWTOcA06H3PxUbYvQ7f%2FmifyHb5O3VnkyOkBX2lnSbTSRlmbyZsQ38HcDRj2sdFsIAIiAQNGaVhf; d_ticket=25c5cbed197e966110d4feebd8d8119c5d194; passport_assist_user=Cjwau2ar_NwRLC2ixe_dhXEsoz57I0-vvhjHeqzg7NGSJb3XgxozLXMU6o6X4ZX-i0w3fSwZVQNc8dKuF3oaSgo8YYC0Rbvz-jb5IOJxnIzJG1_NJ7Wf--FKRoEBxu0HCHsSlN7jKH1Zbo3BncH8ulFtJGlSYTG_nXnV2EUFENLB3A0Yia_WVCABIgEDcI9m4A%3D%3D; n_mh=BLcApAJyDWG0xwZfiZl8GXR4GDSJyJhMmvIui04m6B4; sso_uid_tt=8a4a808434dbc695981896bb660f48a7; sso_uid_tt_ss=8a4a808434dbc695981896bb660f48a7; toutiao_sso_user=21e6ffb53c4c52418cbfd8df8ad99424; toutiao_sso_user_ss=21e6ffb53c4c52418cbfd8df8ad99424; sid_ucp_sso_v1=1.0.0-KDJjNDk0MTRhZTE0OWUxZDZjNjYzZmQzNzkwNzhkMTgzZmY5MWFiNzEKHwjP0NbwvgIQyP6utwYY2hYgDDCZg-HSBTgGQPQHSAYaAmxmIiAyMWU2ZmZiNTNjNGM1MjQxOGNiZmQ4ZGY4YWQ5OTQyNA; ssid_ucp_sso_v1=1.0.0-KDJjNDk0MTRhZTE0OWUxZDZjNjYzZmQzNzkwNzhkMTgzZmY5MWFiNzEKHwjP0NbwvgIQyP6utwYY2hYgDDCZg-HSBTgGQPQHSAYaAmxmIiAyMWU2ZmZiNTNjNGM1MjQxOGNiZmQ4ZGY4YWQ5OTQyNA; passport_auth_status=92a184ef93782587f77f96f0d3e3a4a4%2C; passport_auth_status_ss=92a184ef93782587f77f96f0d3e3a4a4%2C; uid_tt=df03ed6673711ac4872b97f699d2c126; uid_tt_ss=df03ed6673711ac4872b97f699d2c126; sid_tt=00f18b0d75ccbf358fca0db35098911f; sessionid=00f18b0d75ccbf358fca0db35098911f; sessionid_ss=00f18b0d75ccbf358fca0db35098911f; is_staff_user=false; oc_login_type=LOGIN_PERSON; _bd_ticket_crypt_doamin=2; _bd_ticket_crypt_cookie=99b7db74019f20ab7bf1d86e143f2b35; __security_server_data_status=1; sid_guard=00f18b0d75ccbf358fca0db35098911f%7C1726725964%7C5183999%7CMon%2C+18-Nov-2024+06%3A06%3A03+GMT; sid_ucp_v1=1.0.0-KDIxNDQyNWE2MTUxNjllZTE1NWY2OTFmMjE4NGQ5OGI1YmM3ZTU0YzEKGQjP0NbwvgIQzP6utwYY2hYgDDgGQPQHSAQaAmxxIiAwMGYxOGIwZDc1Y2NiZjM1OGZjYTBkYjM1MDk4OTExZg; ssid_ucp_v1=1.0.0-KDIxNDQyNWE2MTUxNjllZTE1NWY2OTFmMjE4NGQ5OGI1YmM3ZTU0YzEKGQjP0NbwvgIQzP6utwYY2hYgDDgGQPQHSAQaAmxxIiAwMGYxOGIwZDc1Y2NiZjM1OGZjYTBkYjM1MDk4OTExZg; _tea_utm_cache_2906=undefined; UIFID_TEMP=63dd93172fb5fdaa8078f33ae0ba1106c8e3f1f870eecbc4b54b497d452c351705d991b09847cfb4e77e5c449a51f49580f9c5a16670964a5380bc79407b9b74366582d28cba029ddf57a51322e5eca6; hevc_supported=true; home_can_add_dy_2_desktop=%220%22; stream_recommend_feed_params=%22%7B%5C%22cookie_enabled%5C%22%3Atrue%2C%5C%22screen_width%5C%22%3A1512%2C%5C%22screen_height%5C%22%3A982%2C%5C%22browser_online%5C%22%3Atrue%2C%5C%22cpu_core_num%5C%22%3A10%2C%5C%22device_memory%5C%22%3A8%2C%5C%22downlink%5C%22%3A10%2C%5C%22effective_type%5C%22%3A%5C%224g%5C%22%2C%5C%22round_trip_time%5C%22%3A150%7D%22; volume_info=%7B%22isUserMute%22%3Afalse%2C%22isMute%22%3Afalse%2C%22volume%22%3A0.5%7D; store-region=cn-zj; store-region-src=uid; odin_tt=a8f584d06370ff960297a6cb064b9eb953f6d5f44874216d54cf89851d2451e465ff49b57cd04e9f5ef43f01ffd4b0f7; csrf_session_id=339011d5e7f539f9baa9fb3c254d7c53; tt_scid=MaocLE5kcYrce7cj1eL-HE6Re1PpGoIFwvWb2IUmJeJ53NcUl.o0g2Fsx6VG.DRt24bd; ttwid=1%7CTRAXmhXVJXjlgpVEpz7ZDIldcnMWzxLd67SkLly3k9o%7C1728566677%7C3d98367cc7d5e8250db3fe9de047b53fa3fc4777ccb9b314c64134bb412901da; bd_ticket_guard_client_data=eyJiZC10aWNrZXQtZ3VhcmQtdmVyc2lvbiI6MiwiYmQtdGlja2V0LWd1YXJkLWl0ZXJhdGlvbi12ZXJzaW9uIjoxLCJiZC10aWNrZXQtZ3VhcmQtcmVlLXB1YmxpYy1rZXkiOiJCSTBZcGY4WDRSejBhWWZGZmh2S1RpRGdSMkR2ak1XbTRqSUZFTUdJeGRzdmh2Q2tONkxVYVlDSGJkcnpVaEFyYlVhUUh0YTZMcmdQY0NDek9XZG8xTjg9IiwiYmQtdGlja2V0LWd1YXJkLXdlYi12ZXJzaW9uIjoyfQ%3D%3D; cc_request_t=6dbd5150-e6e7-489d-9be0-c9745ef79774; msToken=0b3t_pL0qqnx1_OjB9nbqTPqxfv-EIGH7WkHugPLgqpAVNElH_n_Y7yFnWjWs0H4f_vOZGcMs5s5Xm2XhPw8FaL3y1vIlj1ro9gp5ZAi6oMoWBSuwt-lc3IrpGRF; passport_fe_beating_status=true"
            "Cookie": self.get_cookies()

        }

        info_data = pd.read_sql(
            "select * from data_warehouse_douyin_aweme_info_data where douyin_account_id='{}'".format(douyin_unique_id),
            self.mysql_config())
        data_list = [info_data.loc[i].to_dict() for i in info_data.index.values]

        play_finish_ratio_list = []
        for ii in data_list:
            play_avg_time = {
                'index_type': '7',
                'index_type_key': 'play_finish_ratio',
                ##时间区间
                'index_data_window': type_time,
                'item_id': ii['aweme_id']
            }
            play_avg_time_url = 'https://creator.douyin.com/web/api/creator/data/item/option/?aid=2906&app_name=aweme_creator_platform&device_platform=web&referer=https:%2F%2Fcreator.douyin.com%2Fcreator-micro%2Fcontent%2Fmanage&user_agent=Mozilla%2F5.0+(Macintosh%3B+Intel+Mac+OS+X+10_15_7)+AppleWebKit%2F537.36+(KHTML,+like+Gecko)+Chrome%2F129.0.0.0+Safari%2F537.36&cookie_enabled=true&screen_width=1512&screen_height=982&browser_language=zh-CN&browser_platform=MacIntel&browser_name=Mozilla&browser_version=5.0+(Macintosh%3B+Intel+Mac+OS+X+10_15_7)+AppleWebKit%2F537.36+(KHTML,+like+Gecko)+Chrome%2F129.0.0.0+Safari%2F537.36&browser_online=true&timezone_name=Asia%2FShanghai&publish_time=1727942387&mcn_type=0&msToken=fMVoKDIG88LQkkhtl2Z2GbcBjXgP6Y3l5rsd6FJQZfqV6CuXPyT_HshGh9aaEBH5CN_WmtBbg00sjNJqbuuWNU0D0II5VqurOkN4YUoIYthyFC7-6yj5np7flZab&X-Bogus=DFSzswVY5EtANj1qtB83QQIm4L7z&_signature=_02B4Z6wo00001wIj5TgAAIDC4ugu6GY3Aj8CI-GAAKeZp63bpOSXZvhVLh6E1A3zWFbmpUBBtHF98pdXyBXNzr.2cEWkWcFcxGteN-zyQizVm8YmqdBcaOMo.VErChNYlBVgLMn47WJObam51e'
            play_avg_time_get = requests.get(play_avg_time_url, headers=headers, params=play_avg_time)
            play_avg_time_get = play_avg_time_get.json()
            # print(work_detail_hour_json)
            play_finish_ratio_list.append({"play_finish_ratio": play_avg_time_get['trend_data']})
        play_avg_time_frame = pd.DataFrame(play_finish_ratio_list)
        play_avg_time_frame = play_avg_time_frame.astype("str")
        return play_avg_time_frame
            # pd.io.sql.to_sql(play_avg_time_frame, 'play_finish_ratio',
            #                  self.mysql_config(),
            #                  schema='data-warehouse',
            #                  if_exists='append',
            #                  index=False)

    def work_detail_play_digg_count(self,douyin_unique_id,type_time):

        headers = {
            # "Cookie": "bd_ticket_guard_client_web_domain=2; passport_csrf_token=977a960ef4aa9d0b834baa86d9b58842; passport_csrf_token_default=977a960ef4aa9d0b834baa86d9b58842; ttcid=20cfd75cc9864eb1a0fc4942b37dd83551; s_v_web_id=verify_m18w1lgb_pO620Vb4_cHJk_4wuH_84rw_40gjup8tUVXr; passport_mfa_token=CjXFqvCUDzN0XqIwhxrxUZhyedJhjnPSaOq2qRTLO7mVmAttyrRmSJuVvQtKu1VK3kENFwp6%2BRpKCjx%2F25E%2B6i1CkYs3zReZmeDc9UNG6uNxqWTOcA06H3PxUbYvQ7f%2FmifyHb5O3VnkyOkBX2lnSbTSRlmbyZsQ38HcDRj2sdFsIAIiAQNGaVhf; d_ticket=25c5cbed197e966110d4feebd8d8119c5d194; passport_assist_user=Cjwau2ar_NwRLC2ixe_dhXEsoz57I0-vvhjHeqzg7NGSJb3XgxozLXMU6o6X4ZX-i0w3fSwZVQNc8dKuF3oaSgo8YYC0Rbvz-jb5IOJxnIzJG1_NJ7Wf--FKRoEBxu0HCHsSlN7jKH1Zbo3BncH8ulFtJGlSYTG_nXnV2EUFENLB3A0Yia_WVCABIgEDcI9m4A%3D%3D; n_mh=BLcApAJyDWG0xwZfiZl8GXR4GDSJyJhMmvIui04m6B4; sso_uid_tt=8a4a808434dbc695981896bb660f48a7; sso_uid_tt_ss=8a4a808434dbc695981896bb660f48a7; toutiao_sso_user=21e6ffb53c4c52418cbfd8df8ad99424; toutiao_sso_user_ss=21e6ffb53c4c52418cbfd8df8ad99424; sid_ucp_sso_v1=1.0.0-KDJjNDk0MTRhZTE0OWUxZDZjNjYzZmQzNzkwNzhkMTgzZmY5MWFiNzEKHwjP0NbwvgIQyP6utwYY2hYgDDCZg-HSBTgGQPQHSAYaAmxmIiAyMWU2ZmZiNTNjNGM1MjQxOGNiZmQ4ZGY4YWQ5OTQyNA; ssid_ucp_sso_v1=1.0.0-KDJjNDk0MTRhZTE0OWUxZDZjNjYzZmQzNzkwNzhkMTgzZmY5MWFiNzEKHwjP0NbwvgIQyP6utwYY2hYgDDCZg-HSBTgGQPQHSAYaAmxmIiAyMWU2ZmZiNTNjNGM1MjQxOGNiZmQ4ZGY4YWQ5OTQyNA; passport_auth_status=92a184ef93782587f77f96f0d3e3a4a4%2C; passport_auth_status_ss=92a184ef93782587f77f96f0d3e3a4a4%2C; uid_tt=df03ed6673711ac4872b97f699d2c126; uid_tt_ss=df03ed6673711ac4872b97f699d2c126; sid_tt=00f18b0d75ccbf358fca0db35098911f; sessionid=00f18b0d75ccbf358fca0db35098911f; sessionid_ss=00f18b0d75ccbf358fca0db35098911f; is_staff_user=false; oc_login_type=LOGIN_PERSON; _bd_ticket_crypt_doamin=2; _bd_ticket_crypt_cookie=99b7db74019f20ab7bf1d86e143f2b35; __security_server_data_status=1; sid_guard=00f18b0d75ccbf358fca0db35098911f%7C1726725964%7C5183999%7CMon%2C+18-Nov-2024+06%3A06%3A03+GMT; sid_ucp_v1=1.0.0-KDIxNDQyNWE2MTUxNjllZTE1NWY2OTFmMjE4NGQ5OGI1YmM3ZTU0YzEKGQjP0NbwvgIQzP6utwYY2hYgDDgGQPQHSAQaAmxxIiAwMGYxOGIwZDc1Y2NiZjM1OGZjYTBkYjM1MDk4OTExZg; ssid_ucp_v1=1.0.0-KDIxNDQyNWE2MTUxNjllZTE1NWY2OTFmMjE4NGQ5OGI1YmM3ZTU0YzEKGQjP0NbwvgIQzP6utwYY2hYgDDgGQPQHSAQaAmxxIiAwMGYxOGIwZDc1Y2NiZjM1OGZjYTBkYjM1MDk4OTExZg; _tea_utm_cache_2906=undefined; UIFID_TEMP=63dd93172fb5fdaa8078f33ae0ba1106c8e3f1f870eecbc4b54b497d452c351705d991b09847cfb4e77e5c449a51f49580f9c5a16670964a5380bc79407b9b74366582d28cba029ddf57a51322e5eca6; hevc_supported=true; home_can_add_dy_2_desktop=%220%22; stream_recommend_feed_params=%22%7B%5C%22cookie_enabled%5C%22%3Atrue%2C%5C%22screen_width%5C%22%3A1512%2C%5C%22screen_height%5C%22%3A982%2C%5C%22browser_online%5C%22%3Atrue%2C%5C%22cpu_core_num%5C%22%3A10%2C%5C%22device_memory%5C%22%3A8%2C%5C%22downlink%5C%22%3A10%2C%5C%22effective_type%5C%22%3A%5C%224g%5C%22%2C%5C%22round_trip_time%5C%22%3A150%7D%22; volume_info=%7B%22isUserMute%22%3Afalse%2C%22isMute%22%3Afalse%2C%22volume%22%3A0.5%7D; store-region=cn-zj; store-region-src=uid; odin_tt=a8f584d06370ff960297a6cb064b9eb953f6d5f44874216d54cf89851d2451e465ff49b57cd04e9f5ef43f01ffd4b0f7; csrf_session_id=339011d5e7f539f9baa9fb3c254d7c53; tt_scid=MaocLE5kcYrce7cj1eL-HE6Re1PpGoIFwvWb2IUmJeJ53NcUl.o0g2Fsx6VG.DRt24bd; ttwid=1%7CTRAXmhXVJXjlgpVEpz7ZDIldcnMWzxLd67SkLly3k9o%7C1728566677%7C3d98367cc7d5e8250db3fe9de047b53fa3fc4777ccb9b314c64134bb412901da; bd_ticket_guard_client_data=eyJiZC10aWNrZXQtZ3VhcmQtdmVyc2lvbiI6MiwiYmQtdGlja2V0LWd1YXJkLWl0ZXJhdGlvbi12ZXJzaW9uIjoxLCJiZC10aWNrZXQtZ3VhcmQtcmVlLXB1YmxpYy1rZXkiOiJCSTBZcGY4WDRSejBhWWZGZmh2S1RpRGdSMkR2ak1XbTRqSUZFTUdJeGRzdmh2Q2tONkxVYVlDSGJkcnpVaEFyYlVhUUh0YTZMcmdQY0NDek9XZG8xTjg9IiwiYmQtdGlja2V0LWd1YXJkLXdlYi12ZXJzaW9uIjoyfQ%3D%3D; cc_request_t=6dbd5150-e6e7-489d-9be0-c9745ef79774; msToken=0b3t_pL0qqnx1_OjB9nbqTPqxfv-EIGH7WkHugPLgqpAVNElH_n_Y7yFnWjWs0H4f_vOZGcMs5s5Xm2XhPw8FaL3y1vIlj1ro9gp5ZAi6oMoWBSuwt-lc3IrpGRF; passport_fe_beating_status=true"
            "Cookie": self.get_cookies()

        }

        info_data = pd.read_sql(
            "select * from data_warehouse_douyin_aweme_info_data where douyin_account_id='{}'".format(douyin_unique_id),
            self.mysql_config())
        data_list = [info_data.loc[i].to_dict() for i in info_data.index.values]

        digg_count_list = []
        for ii in data_list:
            play_avg_time = {
                'index_type': '3',
                'index_type_key': 'digg_count',
                ##时间区间
                'index_data_window': type_time,
                'item_id': ii['aweme_id']
            }
            play_avg_time_url = 'https://creator.douyin.com/web/api/creator/data/item/option/?aid=2906&app_name=aweme_creator_platform&device_platform=web&referer=https:%2F%2Fcreator.douyin.com%2Fcreator-micro%2Fcontent%2Fmanage&user_agent=Mozilla%2F5.0+(Macintosh%3B+Intel+Mac+OS+X+10_15_7)+AppleWebKit%2F537.36+(KHTML,+like+Gecko)+Chrome%2F129.0.0.0+Safari%2F537.36&cookie_enabled=true&screen_width=1512&screen_height=982&browser_language=zh-CN&browser_platform=MacIntel&browser_name=Mozilla&browser_version=5.0+(Macintosh%3B+Intel+Mac+OS+X+10_15_7)+AppleWebKit%2F537.36+(KHTML,+like+Gecko)+Chrome%2F129.0.0.0+Safari%2F537.36&browser_online=true&timezone_name=Asia%2FShanghai&publish_time=1727942387&mcn_type=0&msToken=fMVoKDIG88LQkkhtl2Z2GbcBjXgP6Y3l5rsd6FJQZfqV6CuXPyT_HshGh9aaEBH5CN_WmtBbg00sjNJqbuuWNU0D0II5VqurOkN4YUoIYthyFC7-6yj5np7flZab&X-Bogus=DFSzswVY5EtANj1qtB83QQIm4L7z&_signature=_02B4Z6wo00001wIj5TgAAIDC4ugu6GY3Aj8CI-GAAKeZp63bpOSXZvhVLh6E1A3zWFbmpUBBtHF98pdXyBXNzr.2cEWkWcFcxGteN-zyQizVm8YmqdBcaOMo.VErChNYlBVgLMn47WJObam51e'
            play_avg_time_get = requests.get(play_avg_time_url, headers=headers, params=play_avg_time)
            play_avg_time_get = play_avg_time_get.json()
            # print(work_detail_hour_json)
            digg_count_list.append({"digg_count": play_avg_time_get['trend_data']})
        play_avg_time_frame = pd.DataFrame(digg_count_list)
        play_avg_time_frame = play_avg_time_frame.astype("str")
        return play_avg_time_frame
            # pd.io.sql.to_sql(play_avg_time_frame, 'digg_count',
            #                  self.mysql_config(),
            #                  schema='data-warehouse',
            #                  if_exists='append',
            #                  index=False)

    def work_detail_play_comment_count(self,douyin_unique_id,type_time):

        headers = {
            # "Cookie": "bd_ticket_guard_client_web_domain=2; passport_csrf_token=977a960ef4aa9d0b834baa86d9b58842; passport_csrf_token_default=977a960ef4aa9d0b834baa86d9b58842; ttcid=20cfd75cc9864eb1a0fc4942b37dd83551; s_v_web_id=verify_m18w1lgb_pO620Vb4_cHJk_4wuH_84rw_40gjup8tUVXr; passport_mfa_token=CjXFqvCUDzN0XqIwhxrxUZhyedJhjnPSaOq2qRTLO7mVmAttyrRmSJuVvQtKu1VK3kENFwp6%2BRpKCjx%2F25E%2B6i1CkYs3zReZmeDc9UNG6uNxqWTOcA06H3PxUbYvQ7f%2FmifyHb5O3VnkyOkBX2lnSbTSRlmbyZsQ38HcDRj2sdFsIAIiAQNGaVhf; d_ticket=25c5cbed197e966110d4feebd8d8119c5d194; passport_assist_user=Cjwau2ar_NwRLC2ixe_dhXEsoz57I0-vvhjHeqzg7NGSJb3XgxozLXMU6o6X4ZX-i0w3fSwZVQNc8dKuF3oaSgo8YYC0Rbvz-jb5IOJxnIzJG1_NJ7Wf--FKRoEBxu0HCHsSlN7jKH1Zbo3BncH8ulFtJGlSYTG_nXnV2EUFENLB3A0Yia_WVCABIgEDcI9m4A%3D%3D; n_mh=BLcApAJyDWG0xwZfiZl8GXR4GDSJyJhMmvIui04m6B4; sso_uid_tt=8a4a808434dbc695981896bb660f48a7; sso_uid_tt_ss=8a4a808434dbc695981896bb660f48a7; toutiao_sso_user=21e6ffb53c4c52418cbfd8df8ad99424; toutiao_sso_user_ss=21e6ffb53c4c52418cbfd8df8ad99424; sid_ucp_sso_v1=1.0.0-KDJjNDk0MTRhZTE0OWUxZDZjNjYzZmQzNzkwNzhkMTgzZmY5MWFiNzEKHwjP0NbwvgIQyP6utwYY2hYgDDCZg-HSBTgGQPQHSAYaAmxmIiAyMWU2ZmZiNTNjNGM1MjQxOGNiZmQ4ZGY4YWQ5OTQyNA; ssid_ucp_sso_v1=1.0.0-KDJjNDk0MTRhZTE0OWUxZDZjNjYzZmQzNzkwNzhkMTgzZmY5MWFiNzEKHwjP0NbwvgIQyP6utwYY2hYgDDCZg-HSBTgGQPQHSAYaAmxmIiAyMWU2ZmZiNTNjNGM1MjQxOGNiZmQ4ZGY4YWQ5OTQyNA; passport_auth_status=92a184ef93782587f77f96f0d3e3a4a4%2C; passport_auth_status_ss=92a184ef93782587f77f96f0d3e3a4a4%2C; uid_tt=df03ed6673711ac4872b97f699d2c126; uid_tt_ss=df03ed6673711ac4872b97f699d2c126; sid_tt=00f18b0d75ccbf358fca0db35098911f; sessionid=00f18b0d75ccbf358fca0db35098911f; sessionid_ss=00f18b0d75ccbf358fca0db35098911f; is_staff_user=false; oc_login_type=LOGIN_PERSON; _bd_ticket_crypt_doamin=2; _bd_ticket_crypt_cookie=99b7db74019f20ab7bf1d86e143f2b35; __security_server_data_status=1; sid_guard=00f18b0d75ccbf358fca0db35098911f%7C1726725964%7C5183999%7CMon%2C+18-Nov-2024+06%3A06%3A03+GMT; sid_ucp_v1=1.0.0-KDIxNDQyNWE2MTUxNjllZTE1NWY2OTFmMjE4NGQ5OGI1YmM3ZTU0YzEKGQjP0NbwvgIQzP6utwYY2hYgDDgGQPQHSAQaAmxxIiAwMGYxOGIwZDc1Y2NiZjM1OGZjYTBkYjM1MDk4OTExZg; ssid_ucp_v1=1.0.0-KDIxNDQyNWE2MTUxNjllZTE1NWY2OTFmMjE4NGQ5OGI1YmM3ZTU0YzEKGQjP0NbwvgIQzP6utwYY2hYgDDgGQPQHSAQaAmxxIiAwMGYxOGIwZDc1Y2NiZjM1OGZjYTBkYjM1MDk4OTExZg; _tea_utm_cache_2906=undefined; UIFID_TEMP=63dd93172fb5fdaa8078f33ae0ba1106c8e3f1f870eecbc4b54b497d452c351705d991b09847cfb4e77e5c449a51f49580f9c5a16670964a5380bc79407b9b74366582d28cba029ddf57a51322e5eca6; hevc_supported=true; home_can_add_dy_2_desktop=%220%22; stream_recommend_feed_params=%22%7B%5C%22cookie_enabled%5C%22%3Atrue%2C%5C%22screen_width%5C%22%3A1512%2C%5C%22screen_height%5C%22%3A982%2C%5C%22browser_online%5C%22%3Atrue%2C%5C%22cpu_core_num%5C%22%3A10%2C%5C%22device_memory%5C%22%3A8%2C%5C%22downlink%5C%22%3A10%2C%5C%22effective_type%5C%22%3A%5C%224g%5C%22%2C%5C%22round_trip_time%5C%22%3A150%7D%22; volume_info=%7B%22isUserMute%22%3Afalse%2C%22isMute%22%3Afalse%2C%22volume%22%3A0.5%7D; store-region=cn-zj; store-region-src=uid; odin_tt=a8f584d06370ff960297a6cb064b9eb953f6d5f44874216d54cf89851d2451e465ff49b57cd04e9f5ef43f01ffd4b0f7; csrf_session_id=339011d5e7f539f9baa9fb3c254d7c53; tt_scid=MaocLE5kcYrce7cj1eL-HE6Re1PpGoIFwvWb2IUmJeJ53NcUl.o0g2Fsx6VG.DRt24bd; ttwid=1%7CTRAXmhXVJXjlgpVEpz7ZDIldcnMWzxLd67SkLly3k9o%7C1728566677%7C3d98367cc7d5e8250db3fe9de047b53fa3fc4777ccb9b314c64134bb412901da; bd_ticket_guard_client_data=eyJiZC10aWNrZXQtZ3VhcmQtdmVyc2lvbiI6MiwiYmQtdGlja2V0LWd1YXJkLWl0ZXJhdGlvbi12ZXJzaW9uIjoxLCJiZC10aWNrZXQtZ3VhcmQtcmVlLXB1YmxpYy1rZXkiOiJCSTBZcGY4WDRSejBhWWZGZmh2S1RpRGdSMkR2ak1XbTRqSUZFTUdJeGRzdmh2Q2tONkxVYVlDSGJkcnpVaEFyYlVhUUh0YTZMcmdQY0NDek9XZG8xTjg9IiwiYmQtdGlja2V0LWd1YXJkLXdlYi12ZXJzaW9uIjoyfQ%3D%3D; cc_request_t=6dbd5150-e6e7-489d-9be0-c9745ef79774; msToken=0b3t_pL0qqnx1_OjB9nbqTPqxfv-EIGH7WkHugPLgqpAVNElH_n_Y7yFnWjWs0H4f_vOZGcMs5s5Xm2XhPw8FaL3y1vIlj1ro9gp5ZAi6oMoWBSuwt-lc3IrpGRF; passport_fe_beating_status=true"
            "Cookie": self.get_cookies()

        }

        info_data = pd.read_sql(
            "select * from data_warehouse_douyin_aweme_info_data where douyin_account_id='{}'".format(douyin_unique_id),
            self.mysql_config())
        data_list = [info_data.loc[i].to_dict() for i in info_data.index.values]

        comment_count_list = []
        for ii in data_list:
            play_avg_time = {
                'index_type': '4',
                'index_type_key': 'comment_count',
                ##时间区间
                'index_data_window': type_time,
                'item_id': ii['aweme_id']
            }
            play_avg_time_url = 'https://creator.douyin.com/web/api/creator/data/item/option/?aid=2906&app_name=aweme_creator_platform&device_platform=web&referer=https:%2F%2Fcreator.douyin.com%2Fcreator-micro%2Fcontent%2Fmanage&user_agent=Mozilla%2F5.0+(Macintosh%3B+Intel+Mac+OS+X+10_15_7)+AppleWebKit%2F537.36+(KHTML,+like+Gecko)+Chrome%2F129.0.0.0+Safari%2F537.36&cookie_enabled=true&screen_width=1512&screen_height=982&browser_language=zh-CN&browser_platform=MacIntel&browser_name=Mozilla&browser_version=5.0+(Macintosh%3B+Intel+Mac+OS+X+10_15_7)+AppleWebKit%2F537.36+(KHTML,+like+Gecko)+Chrome%2F129.0.0.0+Safari%2F537.36&browser_online=true&timezone_name=Asia%2FShanghai&publish_time=1727942387&mcn_type=0&msToken=fMVoKDIG88LQkkhtl2Z2GbcBjXgP6Y3l5rsd6FJQZfqV6CuXPyT_HshGh9aaEBH5CN_WmtBbg00sjNJqbuuWNU0D0II5VqurOkN4YUoIYthyFC7-6yj5np7flZab&X-Bogus=DFSzswVY5EtANj1qtB83QQIm4L7z&_signature=_02B4Z6wo00001wIj5TgAAIDC4ugu6GY3Aj8CI-GAAKeZp63bpOSXZvhVLh6E1A3zWFbmpUBBtHF98pdXyBXNzr.2cEWkWcFcxGteN-zyQizVm8YmqdBcaOMo.VErChNYlBVgLMn47WJObam51e'
            play_avg_time_get = requests.get(play_avg_time_url, headers=headers, params=play_avg_time)
            play_avg_time_get = play_avg_time_get.json()
            # print(work_detail_hour_json)
            comment_count_list.append({"comment_count": play_avg_time_get['trend_data']})
        play_avg_time_frame = pd.DataFrame(comment_count_list)
        play_avg_time_frame = play_avg_time_frame.astype("str")
        return play_avg_time_frame
            # pd.io.sql.to_sql(play_avg_time_frame, 'comment_count',
            #                  self.mysql_config(),
            #                  schema='data-warehouse',
            #                  if_exists='append',
            #                  index=False)

    def work_detail_play_share_count(self,douyin_unique_id,type_time):

        headers = {
            # "Cookie": "bd_ticket_guard_client_web_domain=2; passport_csrf_token=977a960ef4aa9d0b834baa86d9b58842; passport_csrf_token_default=977a960ef4aa9d0b834baa86d9b58842; ttcid=20cfd75cc9864eb1a0fc4942b37dd83551; s_v_web_id=verify_m18w1lgb_pO620Vb4_cHJk_4wuH_84rw_40gjup8tUVXr; passport_mfa_token=CjXFqvCUDzN0XqIwhxrxUZhyedJhjnPSaOq2qRTLO7mVmAttyrRmSJuVvQtKu1VK3kENFwp6%2BRpKCjx%2F25E%2B6i1CkYs3zReZmeDc9UNG6uNxqWTOcA06H3PxUbYvQ7f%2FmifyHb5O3VnkyOkBX2lnSbTSRlmbyZsQ38HcDRj2sdFsIAIiAQNGaVhf; d_ticket=25c5cbed197e966110d4feebd8d8119c5d194; passport_assist_user=Cjwau2ar_NwRLC2ixe_dhXEsoz57I0-vvhjHeqzg7NGSJb3XgxozLXMU6o6X4ZX-i0w3fSwZVQNc8dKuF3oaSgo8YYC0Rbvz-jb5IOJxnIzJG1_NJ7Wf--FKRoEBxu0HCHsSlN7jKH1Zbo3BncH8ulFtJGlSYTG_nXnV2EUFENLB3A0Yia_WVCABIgEDcI9m4A%3D%3D; n_mh=BLcApAJyDWG0xwZfiZl8GXR4GDSJyJhMmvIui04m6B4; sso_uid_tt=8a4a808434dbc695981896bb660f48a7; sso_uid_tt_ss=8a4a808434dbc695981896bb660f48a7; toutiao_sso_user=21e6ffb53c4c52418cbfd8df8ad99424; toutiao_sso_user_ss=21e6ffb53c4c52418cbfd8df8ad99424; sid_ucp_sso_v1=1.0.0-KDJjNDk0MTRhZTE0OWUxZDZjNjYzZmQzNzkwNzhkMTgzZmY5MWFiNzEKHwjP0NbwvgIQyP6utwYY2hYgDDCZg-HSBTgGQPQHSAYaAmxmIiAyMWU2ZmZiNTNjNGM1MjQxOGNiZmQ4ZGY4YWQ5OTQyNA; ssid_ucp_sso_v1=1.0.0-KDJjNDk0MTRhZTE0OWUxZDZjNjYzZmQzNzkwNzhkMTgzZmY5MWFiNzEKHwjP0NbwvgIQyP6utwYY2hYgDDCZg-HSBTgGQPQHSAYaAmxmIiAyMWU2ZmZiNTNjNGM1MjQxOGNiZmQ4ZGY4YWQ5OTQyNA; passport_auth_status=92a184ef93782587f77f96f0d3e3a4a4%2C; passport_auth_status_ss=92a184ef93782587f77f96f0d3e3a4a4%2C; uid_tt=df03ed6673711ac4872b97f699d2c126; uid_tt_ss=df03ed6673711ac4872b97f699d2c126; sid_tt=00f18b0d75ccbf358fca0db35098911f; sessionid=00f18b0d75ccbf358fca0db35098911f; sessionid_ss=00f18b0d75ccbf358fca0db35098911f; is_staff_user=false; oc_login_type=LOGIN_PERSON; _bd_ticket_crypt_doamin=2; _bd_ticket_crypt_cookie=99b7db74019f20ab7bf1d86e143f2b35; __security_server_data_status=1; sid_guard=00f18b0d75ccbf358fca0db35098911f%7C1726725964%7C5183999%7CMon%2C+18-Nov-2024+06%3A06%3A03+GMT; sid_ucp_v1=1.0.0-KDIxNDQyNWE2MTUxNjllZTE1NWY2OTFmMjE4NGQ5OGI1YmM3ZTU0YzEKGQjP0NbwvgIQzP6utwYY2hYgDDgGQPQHSAQaAmxxIiAwMGYxOGIwZDc1Y2NiZjM1OGZjYTBkYjM1MDk4OTExZg; ssid_ucp_v1=1.0.0-KDIxNDQyNWE2MTUxNjllZTE1NWY2OTFmMjE4NGQ5OGI1YmM3ZTU0YzEKGQjP0NbwvgIQzP6utwYY2hYgDDgGQPQHSAQaAmxxIiAwMGYxOGIwZDc1Y2NiZjM1OGZjYTBkYjM1MDk4OTExZg; _tea_utm_cache_2906=undefined; UIFID_TEMP=63dd93172fb5fdaa8078f33ae0ba1106c8e3f1f870eecbc4b54b497d452c351705d991b09847cfb4e77e5c449a51f49580f9c5a16670964a5380bc79407b9b74366582d28cba029ddf57a51322e5eca6; hevc_supported=true; home_can_add_dy_2_desktop=%220%22; stream_recommend_feed_params=%22%7B%5C%22cookie_enabled%5C%22%3Atrue%2C%5C%22screen_width%5C%22%3A1512%2C%5C%22screen_height%5C%22%3A982%2C%5C%22browser_online%5C%22%3Atrue%2C%5C%22cpu_core_num%5C%22%3A10%2C%5C%22device_memory%5C%22%3A8%2C%5C%22downlink%5C%22%3A10%2C%5C%22effective_type%5C%22%3A%5C%224g%5C%22%2C%5C%22round_trip_time%5C%22%3A150%7D%22; volume_info=%7B%22isUserMute%22%3Afalse%2C%22isMute%22%3Afalse%2C%22volume%22%3A0.5%7D; store-region=cn-zj; store-region-src=uid; odin_tt=a8f584d06370ff960297a6cb064b9eb953f6d5f44874216d54cf89851d2451e465ff49b57cd04e9f5ef43f01ffd4b0f7; csrf_session_id=339011d5e7f539f9baa9fb3c254d7c53; tt_scid=MaocLE5kcYrce7cj1eL-HE6Re1PpGoIFwvWb2IUmJeJ53NcUl.o0g2Fsx6VG.DRt24bd; ttwid=1%7CTRAXmhXVJXjlgpVEpz7ZDIldcnMWzxLd67SkLly3k9o%7C1728566677%7C3d98367cc7d5e8250db3fe9de047b53fa3fc4777ccb9b314c64134bb412901da; bd_ticket_guard_client_data=eyJiZC10aWNrZXQtZ3VhcmQtdmVyc2lvbiI6MiwiYmQtdGlja2V0LWd1YXJkLWl0ZXJhdGlvbi12ZXJzaW9uIjoxLCJiZC10aWNrZXQtZ3VhcmQtcmVlLXB1YmxpYy1rZXkiOiJCSTBZcGY4WDRSejBhWWZGZmh2S1RpRGdSMkR2ak1XbTRqSUZFTUdJeGRzdmh2Q2tONkxVYVlDSGJkcnpVaEFyYlVhUUh0YTZMcmdQY0NDek9XZG8xTjg9IiwiYmQtdGlja2V0LWd1YXJkLXdlYi12ZXJzaW9uIjoyfQ%3D%3D; cc_request_t=6dbd5150-e6e7-489d-9be0-c9745ef79774; msToken=0b3t_pL0qqnx1_OjB9nbqTPqxfv-EIGH7WkHugPLgqpAVNElH_n_Y7yFnWjWs0H4f_vOZGcMs5s5Xm2XhPw8FaL3y1vIlj1ro9gp5ZAi6oMoWBSuwt-lc3IrpGRF; passport_fe_beating_status=true"
            "Cookie": self.get_cookies()

        }

        info_data = pd.read_sql(
            "select * from data_warehouse_douyin_aweme_info_data where douyin_account_id='{}'".format(douyin_unique_id),
            self.mysql_config())
        data_list = [info_data.loc[i].to_dict() for i in info_data.index.values]

        share_count_list = []
        for ii in data_list:
            play_avg_time = {
                'index_type': '5',
                'index_type_key': 'share_count',
                ##时间区间
                'index_data_window': type_time,
                'item_id': ii['aweme_id']
            }
            play_avg_time_url = 'https://creator.douyin.com/web/api/creator/data/item/option/?aid=2906&app_name=aweme_creator_platform&device_platform=web&referer=https:%2F%2Fcreator.douyin.com%2Fcreator-micro%2Fcontent%2Fmanage&user_agent=Mozilla%2F5.0+(Macintosh%3B+Intel+Mac+OS+X+10_15_7)+AppleWebKit%2F537.36+(KHTML,+like+Gecko)+Chrome%2F129.0.0.0+Safari%2F537.36&cookie_enabled=true&screen_width=1512&screen_height=982&browser_language=zh-CN&browser_platform=MacIntel&browser_name=Mozilla&browser_version=5.0+(Macintosh%3B+Intel+Mac+OS+X+10_15_7)+AppleWebKit%2F537.36+(KHTML,+like+Gecko)+Chrome%2F129.0.0.0+Safari%2F537.36&browser_online=true&timezone_name=Asia%2FShanghai&publish_time=1727942387&mcn_type=0&msToken=fMVoKDIG88LQkkhtl2Z2GbcBjXgP6Y3l5rsd6FJQZfqV6CuXPyT_HshGh9aaEBH5CN_WmtBbg00sjNJqbuuWNU0D0II5VqurOkN4YUoIYthyFC7-6yj5np7flZab&X-Bogus=DFSzswVY5EtANj1qtB83QQIm4L7z&_signature=_02B4Z6wo00001wIj5TgAAIDC4ugu6GY3Aj8CI-GAAKeZp63bpOSXZvhVLh6E1A3zWFbmpUBBtHF98pdXyBXNzr.2cEWkWcFcxGteN-zyQizVm8YmqdBcaOMo.VErChNYlBVgLMn47WJObam51e'
            play_avg_time_get = requests.get(play_avg_time_url, headers=headers, params=play_avg_time)
            play_avg_time_get = play_avg_time_get.json()
            # print(work_detail_hour_json)
            share_count_list.append({"share_count": play_avg_time_get['trend_data']})
        play_avg_time_frame = pd.DataFrame(share_count_list)
        play_avg_time_frame = play_avg_time_frame.astype("str")
        return play_avg_time_frame
            # pd.io.sql.to_sql(play_avg_time_frame, 'share_count',
            #                  self.mysql_config(),
            #                  schema='data-warehouse',
            #                  if_exists='append',
            #                  index=False)

    def work_detail_play_new_fans_count(self,douyin_unique_id,type_time):

        headers = {
            # "Cookie": "bd_ticket_guard_client_web_domain=2; passport_csrf_token=977a960ef4aa9d0b834baa86d9b58842; passport_csrf_token_default=977a960ef4aa9d0b834baa86d9b58842; ttcid=20cfd75cc9864eb1a0fc4942b37dd83551; s_v_web_id=verify_m18w1lgb_pO620Vb4_cHJk_4wuH_84rw_40gjup8tUVXr; passport_mfa_token=CjXFqvCUDzN0XqIwhxrxUZhyedJhjnPSaOq2qRTLO7mVmAttyrRmSJuVvQtKu1VK3kENFwp6%2BRpKCjx%2F25E%2B6i1CkYs3zReZmeDc9UNG6uNxqWTOcA06H3PxUbYvQ7f%2FmifyHb5O3VnkyOkBX2lnSbTSRlmbyZsQ38HcDRj2sdFsIAIiAQNGaVhf; d_ticket=25c5cbed197e966110d4feebd8d8119c5d194; passport_assist_user=Cjwau2ar_NwRLC2ixe_dhXEsoz57I0-vvhjHeqzg7NGSJb3XgxozLXMU6o6X4ZX-i0w3fSwZVQNc8dKuF3oaSgo8YYC0Rbvz-jb5IOJxnIzJG1_NJ7Wf--FKRoEBxu0HCHsSlN7jKH1Zbo3BncH8ulFtJGlSYTG_nXnV2EUFENLB3A0Yia_WVCABIgEDcI9m4A%3D%3D; n_mh=BLcApAJyDWG0xwZfiZl8GXR4GDSJyJhMmvIui04m6B4; sso_uid_tt=8a4a808434dbc695981896bb660f48a7; sso_uid_tt_ss=8a4a808434dbc695981896bb660f48a7; toutiao_sso_user=21e6ffb53c4c52418cbfd8df8ad99424; toutiao_sso_user_ss=21e6ffb53c4c52418cbfd8df8ad99424; sid_ucp_sso_v1=1.0.0-KDJjNDk0MTRhZTE0OWUxZDZjNjYzZmQzNzkwNzhkMTgzZmY5MWFiNzEKHwjP0NbwvgIQyP6utwYY2hYgDDCZg-HSBTgGQPQHSAYaAmxmIiAyMWU2ZmZiNTNjNGM1MjQxOGNiZmQ4ZGY4YWQ5OTQyNA; ssid_ucp_sso_v1=1.0.0-KDJjNDk0MTRhZTE0OWUxZDZjNjYzZmQzNzkwNzhkMTgzZmY5MWFiNzEKHwjP0NbwvgIQyP6utwYY2hYgDDCZg-HSBTgGQPQHSAYaAmxmIiAyMWU2ZmZiNTNjNGM1MjQxOGNiZmQ4ZGY4YWQ5OTQyNA; passport_auth_status=92a184ef93782587f77f96f0d3e3a4a4%2C; passport_auth_status_ss=92a184ef93782587f77f96f0d3e3a4a4%2C; uid_tt=df03ed6673711ac4872b97f699d2c126; uid_tt_ss=df03ed6673711ac4872b97f699d2c126; sid_tt=00f18b0d75ccbf358fca0db35098911f; sessionid=00f18b0d75ccbf358fca0db35098911f; sessionid_ss=00f18b0d75ccbf358fca0db35098911f; is_staff_user=false; oc_login_type=LOGIN_PERSON; _bd_ticket_crypt_doamin=2; _bd_ticket_crypt_cookie=99b7db74019f20ab7bf1d86e143f2b35; __security_server_data_status=1; sid_guard=00f18b0d75ccbf358fca0db35098911f%7C1726725964%7C5183999%7CMon%2C+18-Nov-2024+06%3A06%3A03+GMT; sid_ucp_v1=1.0.0-KDIxNDQyNWE2MTUxNjllZTE1NWY2OTFmMjE4NGQ5OGI1YmM3ZTU0YzEKGQjP0NbwvgIQzP6utwYY2hYgDDgGQPQHSAQaAmxxIiAwMGYxOGIwZDc1Y2NiZjM1OGZjYTBkYjM1MDk4OTExZg; ssid_ucp_v1=1.0.0-KDIxNDQyNWE2MTUxNjllZTE1NWY2OTFmMjE4NGQ5OGI1YmM3ZTU0YzEKGQjP0NbwvgIQzP6utwYY2hYgDDgGQPQHSAQaAmxxIiAwMGYxOGIwZDc1Y2NiZjM1OGZjYTBkYjM1MDk4OTExZg; _tea_utm_cache_2906=undefined; UIFID_TEMP=63dd93172fb5fdaa8078f33ae0ba1106c8e3f1f870eecbc4b54b497d452c351705d991b09847cfb4e77e5c449a51f49580f9c5a16670964a5380bc79407b9b74366582d28cba029ddf57a51322e5eca6; hevc_supported=true; home_can_add_dy_2_desktop=%220%22; stream_recommend_feed_params=%22%7B%5C%22cookie_enabled%5C%22%3Atrue%2C%5C%22screen_width%5C%22%3A1512%2C%5C%22screen_height%5C%22%3A982%2C%5C%22browser_online%5C%22%3Atrue%2C%5C%22cpu_core_num%5C%22%3A10%2C%5C%22device_memory%5C%22%3A8%2C%5C%22downlink%5C%22%3A10%2C%5C%22effective_type%5C%22%3A%5C%224g%5C%22%2C%5C%22round_trip_time%5C%22%3A150%7D%22; volume_info=%7B%22isUserMute%22%3Afalse%2C%22isMute%22%3Afalse%2C%22volume%22%3A0.5%7D; store-region=cn-zj; store-region-src=uid; odin_tt=a8f584d06370ff960297a6cb064b9eb953f6d5f44874216d54cf89851d2451e465ff49b57cd04e9f5ef43f01ffd4b0f7; csrf_session_id=339011d5e7f539f9baa9fb3c254d7c53; tt_scid=MaocLE5kcYrce7cj1eL-HE6Re1PpGoIFwvWb2IUmJeJ53NcUl.o0g2Fsx6VG.DRt24bd; ttwid=1%7CTRAXmhXVJXjlgpVEpz7ZDIldcnMWzxLd67SkLly3k9o%7C1728566677%7C3d98367cc7d5e8250db3fe9de047b53fa3fc4777ccb9b314c64134bb412901da; bd_ticket_guard_client_data=eyJiZC10aWNrZXQtZ3VhcmQtdmVyc2lvbiI6MiwiYmQtdGlja2V0LWd1YXJkLWl0ZXJhdGlvbi12ZXJzaW9uIjoxLCJiZC10aWNrZXQtZ3VhcmQtcmVlLXB1YmxpYy1rZXkiOiJCSTBZcGY4WDRSejBhWWZGZmh2S1RpRGdSMkR2ak1XbTRqSUZFTUdJeGRzdmh2Q2tONkxVYVlDSGJkcnpVaEFyYlVhUUh0YTZMcmdQY0NDek9XZG8xTjg9IiwiYmQtdGlja2V0LWd1YXJkLXdlYi12ZXJzaW9uIjoyfQ%3D%3D; cc_request_t=6dbd5150-e6e7-489d-9be0-c9745ef79774; msToken=0b3t_pL0qqnx1_OjB9nbqTPqxfv-EIGH7WkHugPLgqpAVNElH_n_Y7yFnWjWs0H4f_vOZGcMs5s5Xm2XhPw8FaL3y1vIlj1ro9gp5ZAi6oMoWBSuwt-lc3IrpGRF; passport_fe_beating_status=true"
            "Cookie": self.get_cookies()

        }

        info_data = pd.read_sql(
            "select * from data_warehouse_douyin_aweme_info_data where douyin_account_id='{}'".format(douyin_unique_id),
            self.mysql_config())
        data_list = [info_data.loc[i].to_dict() for i in info_data.index.values]

        new_fans_count_list = []
        for ii in data_list:
            play_avg_time = {
                'index_type': '9',
                'index_type_key': 'new_fans_count',
                ##时间区间
                'index_data_window': type_time,
                'item_id': ii['aweme_id']
            }
            play_avg_time_url = 'https://creator.douyin.com/web/api/creator/data/item/option/?aid=2906&app_name=aweme_creator_platform&device_platform=web&referer=https:%2F%2Fcreator.douyin.com%2Fcreator-micro%2Fcontent%2Fmanage&user_agent=Mozilla%2F5.0+(Macintosh%3B+Intel+Mac+OS+X+10_15_7)+AppleWebKit%2F537.36+(KHTML,+like+Gecko)+Chrome%2F129.0.0.0+Safari%2F537.36&cookie_enabled=true&screen_width=1512&screen_height=982&browser_language=zh-CN&browser_platform=MacIntel&browser_name=Mozilla&browser_version=5.0+(Macintosh%3B+Intel+Mac+OS+X+10_15_7)+AppleWebKit%2F537.36+(KHTML,+like+Gecko)+Chrome%2F129.0.0.0+Safari%2F537.36&browser_online=true&timezone_name=Asia%2FShanghai&publish_time=1727942387&mcn_type=0&msToken=fMVoKDIG88LQkkhtl2Z2GbcBjXgP6Y3l5rsd6FJQZfqV6CuXPyT_HshGh9aaEBH5CN_WmtBbg00sjNJqbuuWNU0D0II5VqurOkN4YUoIYthyFC7-6yj5np7flZab&X-Bogus=DFSzswVY5EtANj1qtB83QQIm4L7z&_signature=_02B4Z6wo00001wIj5TgAAIDC4ugu6GY3Aj8CI-GAAKeZp63bpOSXZvhVLh6E1A3zWFbmpUBBtHF98pdXyBXNzr.2cEWkWcFcxGteN-zyQizVm8YmqdBcaOMo.VErChNYlBVgLMn47WJObam51e'
            play_avg_time_get = requests.get(play_avg_time_url, headers=headers, params=play_avg_time)
            play_avg_time_get = play_avg_time_get.json()
            # print(work_detail_hour_json)
            new_fans_count_list.append({"new_fans_count": play_avg_time_get['trend_data']})
        play_avg_time_frame = pd.DataFrame(new_fans_count_list)
        play_avg_time_frame = play_avg_time_frame.astype("str")
        return play_avg_time_frame
            # pd.io.sql.to_sql(play_avg_time_frame, 'new_fans_count',
            #                  self.mysql_config(),
            #                  schema='data-warehouse',
            #                  if_exists='append',
            #                  index=False)

    def work_detail_play_cover_click_rate(self,douyin_unique_id,type_time):
        headers = {
            # "Cookie": "bd_ticket_guard_client_web_domain=2; passport_csrf_token=977a960ef4aa9d0b834baa86d9b58842; passport_csrf_token_default=977a960ef4aa9d0b834baa86d9b58842; ttcid=20cfd75cc9864eb1a0fc4942b37dd83551; s_v_web_id=verify_m18w1lgb_pO620Vb4_cHJk_4wuH_84rw_40gjup8tUVXr; passport_mfa_token=CjXFqvCUDzN0XqIwhxrxUZhyedJhjnPSaOq2qRTLO7mVmAttyrRmSJuVvQtKu1VK3kENFwp6%2BRpKCjx%2F25E%2B6i1CkYs3zReZmeDc9UNG6uNxqWTOcA06H3PxUbYvQ7f%2FmifyHb5O3VnkyOkBX2lnSbTSRlmbyZsQ38HcDRj2sdFsIAIiAQNGaVhf; d_ticket=25c5cbed197e966110d4feebd8d8119c5d194; passport_assist_user=Cjwau2ar_NwRLC2ixe_dhXEsoz57I0-vvhjHeqzg7NGSJb3XgxozLXMU6o6X4ZX-i0w3fSwZVQNc8dKuF3oaSgo8YYC0Rbvz-jb5IOJxnIzJG1_NJ7Wf--FKRoEBxu0HCHsSlN7jKH1Zbo3BncH8ulFtJGlSYTG_nXnV2EUFENLB3A0Yia_WVCABIgEDcI9m4A%3D%3D; n_mh=BLcApAJyDWG0xwZfiZl8GXR4GDSJyJhMmvIui04m6B4; sso_uid_tt=8a4a808434dbc695981896bb660f48a7; sso_uid_tt_ss=8a4a808434dbc695981896bb660f48a7; toutiao_sso_user=21e6ffb53c4c52418cbfd8df8ad99424; toutiao_sso_user_ss=21e6ffb53c4c52418cbfd8df8ad99424; sid_ucp_sso_v1=1.0.0-KDJjNDk0MTRhZTE0OWUxZDZjNjYzZmQzNzkwNzhkMTgzZmY5MWFiNzEKHwjP0NbwvgIQyP6utwYY2hYgDDCZg-HSBTgGQPQHSAYaAmxmIiAyMWU2ZmZiNTNjNGM1MjQxOGNiZmQ4ZGY4YWQ5OTQyNA; ssid_ucp_sso_v1=1.0.0-KDJjNDk0MTRhZTE0OWUxZDZjNjYzZmQzNzkwNzhkMTgzZmY5MWFiNzEKHwjP0NbwvgIQyP6utwYY2hYgDDCZg-HSBTgGQPQHSAYaAmxmIiAyMWU2ZmZiNTNjNGM1MjQxOGNiZmQ4ZGY4YWQ5OTQyNA; passport_auth_status=92a184ef93782587f77f96f0d3e3a4a4%2C; passport_auth_status_ss=92a184ef93782587f77f96f0d3e3a4a4%2C; uid_tt=df03ed6673711ac4872b97f699d2c126; uid_tt_ss=df03ed6673711ac4872b97f699d2c126; sid_tt=00f18b0d75ccbf358fca0db35098911f; sessionid=00f18b0d75ccbf358fca0db35098911f; sessionid_ss=00f18b0d75ccbf358fca0db35098911f; is_staff_user=false; oc_login_type=LOGIN_PERSON; _bd_ticket_crypt_doamin=2; _bd_ticket_crypt_cookie=99b7db74019f20ab7bf1d86e143f2b35; __security_server_data_status=1; sid_guard=00f18b0d75ccbf358fca0db35098911f%7C1726725964%7C5183999%7CMon%2C+18-Nov-2024+06%3A06%3A03+GMT; sid_ucp_v1=1.0.0-KDIxNDQyNWE2MTUxNjllZTE1NWY2OTFmMjE4NGQ5OGI1YmM3ZTU0YzEKGQjP0NbwvgIQzP6utwYY2hYgDDgGQPQHSAQaAmxxIiAwMGYxOGIwZDc1Y2NiZjM1OGZjYTBkYjM1MDk4OTExZg; ssid_ucp_v1=1.0.0-KDIxNDQyNWE2MTUxNjllZTE1NWY2OTFmMjE4NGQ5OGI1YmM3ZTU0YzEKGQjP0NbwvgIQzP6utwYY2hYgDDgGQPQHSAQaAmxxIiAwMGYxOGIwZDc1Y2NiZjM1OGZjYTBkYjM1MDk4OTExZg; _tea_utm_cache_2906=undefined; UIFID_TEMP=63dd93172fb5fdaa8078f33ae0ba1106c8e3f1f870eecbc4b54b497d452c351705d991b09847cfb4e77e5c449a51f49580f9c5a16670964a5380bc79407b9b74366582d28cba029ddf57a51322e5eca6; hevc_supported=true; home_can_add_dy_2_desktop=%220%22; stream_recommend_feed_params=%22%7B%5C%22cookie_enabled%5C%22%3Atrue%2C%5C%22screen_width%5C%22%3A1512%2C%5C%22screen_height%5C%22%3A982%2C%5C%22browser_online%5C%22%3Atrue%2C%5C%22cpu_core_num%5C%22%3A10%2C%5C%22device_memory%5C%22%3A8%2C%5C%22downlink%5C%22%3A10%2C%5C%22effective_type%5C%22%3A%5C%224g%5C%22%2C%5C%22round_trip_time%5C%22%3A150%7D%22; volume_info=%7B%22isUserMute%22%3Afalse%2C%22isMute%22%3Afalse%2C%22volume%22%3A0.5%7D; store-region=cn-zj; store-region-src=uid; odin_tt=a8f584d06370ff960297a6cb064b9eb953f6d5f44874216d54cf89851d2451e465ff49b57cd04e9f5ef43f01ffd4b0f7; csrf_session_id=339011d5e7f539f9baa9fb3c254d7c53; tt_scid=MaocLE5kcYrce7cj1eL-HE6Re1PpGoIFwvWb2IUmJeJ53NcUl.o0g2Fsx6VG.DRt24bd; ttwid=1%7CTRAXmhXVJXjlgpVEpz7ZDIldcnMWzxLd67SkLly3k9o%7C1728566677%7C3d98367cc7d5e8250db3fe9de047b53fa3fc4777ccb9b314c64134bb412901da; bd_ticket_guard_client_data=eyJiZC10aWNrZXQtZ3VhcmQtdmVyc2lvbiI6MiwiYmQtdGlja2V0LWd1YXJkLWl0ZXJhdGlvbi12ZXJzaW9uIjoxLCJiZC10aWNrZXQtZ3VhcmQtcmVlLXB1YmxpYy1rZXkiOiJCSTBZcGY4WDRSejBhWWZGZmh2S1RpRGdSMkR2ak1XbTRqSUZFTUdJeGRzdmh2Q2tONkxVYVlDSGJkcnpVaEFyYlVhUUh0YTZMcmdQY0NDek9XZG8xTjg9IiwiYmQtdGlja2V0LWd1YXJkLXdlYi12ZXJzaW9uIjoyfQ%3D%3D; cc_request_t=6dbd5150-e6e7-489d-9be0-c9745ef79774; msToken=0b3t_pL0qqnx1_OjB9nbqTPqxfv-EIGH7WkHugPLgqpAVNElH_n_Y7yFnWjWs0H4f_vOZGcMs5s5Xm2XhPw8FaL3y1vIlj1ro9gp5ZAi6oMoWBSuwt-lc3IrpGRF; passport_fe_beating_status=true"
            "Cookie": self.get_cookies()

        }

        info_data = pd.read_sql("select * from data_warehouse_douyin_aweme_info_data where douyin_account_id='{}'".format(douyin_unique_id),self.mysql_config())
        data_list = [info_data.loc[i].to_dict() for i in info_data.index.values]

        cover_click_rate_list = []
        for ii in data_list:
            url_trend_map = "https://creator.douyin.com/janus/douyin/creator/data/item_analysis/metrics_trend?aid=2906&app_name=aweme_creator_platform&device_platform=web&referer=https:%2F%2Fcreator.douyin.com%2Fcreator-micro%2Fcontent%2Fmanage&user_agent=Mozilla%2F5.0+(Macintosh%3B+Intel+Mac+OS+X+10_15_7)+AppleWebKit%2F537.36+(KHTML,+like+Gecko)+Chrome%2F129.0.0.0+Safari%2F537.36&cookie_enabled=true&screen_width=1512&screen_height=982&browser_language=zh-CN&browser_platform=MacIntel&browser_name=Mozilla&browser_version=5.0+(Macintosh%3B+Intel+Mac+OS+X+10_15_7)+AppleWebKit%2F537.36+(KHTML,+like+Gecko)+Chrome%2F129.0.0.0+Safari%2F537.36&browser_online=true&timezone_name=Asia%2FShanghai&trend_type=2&metrics_group=[0]"
            map_list = ["cover_click_rate","favorite_count","danmaku_count"]

            # t_map_list = []
            data_map = {
                "time_unit": type_time,
                "item_id": ii["aweme_id"],
                "metrics": ["cover_click_rate"]
            }

            """封面点击率、弹幕量、收藏量计算"""
            final_data = requests.get(url_trend_map, headers=headers, params=data_map)
            cover_click_rate_list.append({'cover_click_rate':final_data.json()['trend_map']['cover_click_rate']['0'],"douyin_account_id":douyin_unique_id,"aweme_id":ii["aweme_id"]})
        cover_click_rate_frame = pd.DataFrame(cover_click_rate_list)
        cover_click_rate_frame = cover_click_rate_frame.astype("str")
        return cover_click_rate_frame

    def work_detail_play_favorite_count(self,douyin_unique_id,type_time):
        headers = {
            # "Cookie": "bd_ticket_guard_client_web_domain=2; passport_csrf_token=977a960ef4aa9d0b834baa86d9b58842; passport_csrf_token_default=977a960ef4aa9d0b834baa86d9b58842; ttcid=20cfd75cc9864eb1a0fc4942b37dd83551; s_v_web_id=verify_m18w1lgb_pO620Vb4_cHJk_4wuH_84rw_40gjup8tUVXr; passport_mfa_token=CjXFqvCUDzN0XqIwhxrxUZhyedJhjnPSaOq2qRTLO7mVmAttyrRmSJuVvQtKu1VK3kENFwp6%2BRpKCjx%2F25E%2B6i1CkYs3zReZmeDc9UNG6uNxqWTOcA06H3PxUbYvQ7f%2FmifyHb5O3VnkyOkBX2lnSbTSRlmbyZsQ38HcDRj2sdFsIAIiAQNGaVhf; d_ticket=25c5cbed197e966110d4feebd8d8119c5d194; passport_assist_user=Cjwau2ar_NwRLC2ixe_dhXEsoz57I0-vvhjHeqzg7NGSJb3XgxozLXMU6o6X4ZX-i0w3fSwZVQNc8dKuF3oaSgo8YYC0Rbvz-jb5IOJxnIzJG1_NJ7Wf--FKRoEBxu0HCHsSlN7jKH1Zbo3BncH8ulFtJGlSYTG_nXnV2EUFENLB3A0Yia_WVCABIgEDcI9m4A%3D%3D; n_mh=BLcApAJyDWG0xwZfiZl8GXR4GDSJyJhMmvIui04m6B4; sso_uid_tt=8a4a808434dbc695981896bb660f48a7; sso_uid_tt_ss=8a4a808434dbc695981896bb660f48a7; toutiao_sso_user=21e6ffb53c4c52418cbfd8df8ad99424; toutiao_sso_user_ss=21e6ffb53c4c52418cbfd8df8ad99424; sid_ucp_sso_v1=1.0.0-KDJjNDk0MTRhZTE0OWUxZDZjNjYzZmQzNzkwNzhkMTgzZmY5MWFiNzEKHwjP0NbwvgIQyP6utwYY2hYgDDCZg-HSBTgGQPQHSAYaAmxmIiAyMWU2ZmZiNTNjNGM1MjQxOGNiZmQ4ZGY4YWQ5OTQyNA; ssid_ucp_sso_v1=1.0.0-KDJjNDk0MTRhZTE0OWUxZDZjNjYzZmQzNzkwNzhkMTgzZmY5MWFiNzEKHwjP0NbwvgIQyP6utwYY2hYgDDCZg-HSBTgGQPQHSAYaAmxmIiAyMWU2ZmZiNTNjNGM1MjQxOGNiZmQ4ZGY4YWQ5OTQyNA; passport_auth_status=92a184ef93782587f77f96f0d3e3a4a4%2C; passport_auth_status_ss=92a184ef93782587f77f96f0d3e3a4a4%2C; uid_tt=df03ed6673711ac4872b97f699d2c126; uid_tt_ss=df03ed6673711ac4872b97f699d2c126; sid_tt=00f18b0d75ccbf358fca0db35098911f; sessionid=00f18b0d75ccbf358fca0db35098911f; sessionid_ss=00f18b0d75ccbf358fca0db35098911f; is_staff_user=false; oc_login_type=LOGIN_PERSON; _bd_ticket_crypt_doamin=2; _bd_ticket_crypt_cookie=99b7db74019f20ab7bf1d86e143f2b35; __security_server_data_status=1; sid_guard=00f18b0d75ccbf358fca0db35098911f%7C1726725964%7C5183999%7CMon%2C+18-Nov-2024+06%3A06%3A03+GMT; sid_ucp_v1=1.0.0-KDIxNDQyNWE2MTUxNjllZTE1NWY2OTFmMjE4NGQ5OGI1YmM3ZTU0YzEKGQjP0NbwvgIQzP6utwYY2hYgDDgGQPQHSAQaAmxxIiAwMGYxOGIwZDc1Y2NiZjM1OGZjYTBkYjM1MDk4OTExZg; ssid_ucp_v1=1.0.0-KDIxNDQyNWE2MTUxNjllZTE1NWY2OTFmMjE4NGQ5OGI1YmM3ZTU0YzEKGQjP0NbwvgIQzP6utwYY2hYgDDgGQPQHSAQaAmxxIiAwMGYxOGIwZDc1Y2NiZjM1OGZjYTBkYjM1MDk4OTExZg; _tea_utm_cache_2906=undefined; UIFID_TEMP=63dd93172fb5fdaa8078f33ae0ba1106c8e3f1f870eecbc4b54b497d452c351705d991b09847cfb4e77e5c449a51f49580f9c5a16670964a5380bc79407b9b74366582d28cba029ddf57a51322e5eca6; hevc_supported=true; home_can_add_dy_2_desktop=%220%22; stream_recommend_feed_params=%22%7B%5C%22cookie_enabled%5C%22%3Atrue%2C%5C%22screen_width%5C%22%3A1512%2C%5C%22screen_height%5C%22%3A982%2C%5C%22browser_online%5C%22%3Atrue%2C%5C%22cpu_core_num%5C%22%3A10%2C%5C%22device_memory%5C%22%3A8%2C%5C%22downlink%5C%22%3A10%2C%5C%22effective_type%5C%22%3A%5C%224g%5C%22%2C%5C%22round_trip_time%5C%22%3A150%7D%22; volume_info=%7B%22isUserMute%22%3Afalse%2C%22isMute%22%3Afalse%2C%22volume%22%3A0.5%7D; store-region=cn-zj; store-region-src=uid; odin_tt=a8f584d06370ff960297a6cb064b9eb953f6d5f44874216d54cf89851d2451e465ff49b57cd04e9f5ef43f01ffd4b0f7; csrf_session_id=339011d5e7f539f9baa9fb3c254d7c53; tt_scid=MaocLE5kcYrce7cj1eL-HE6Re1PpGoIFwvWb2IUmJeJ53NcUl.o0g2Fsx6VG.DRt24bd; ttwid=1%7CTRAXmhXVJXjlgpVEpz7ZDIldcnMWzxLd67SkLly3k9o%7C1728566677%7C3d98367cc7d5e8250db3fe9de047b53fa3fc4777ccb9b314c64134bb412901da; bd_ticket_guard_client_data=eyJiZC10aWNrZXQtZ3VhcmQtdmVyc2lvbiI6MiwiYmQtdGlja2V0LWd1YXJkLWl0ZXJhdGlvbi12ZXJzaW9uIjoxLCJiZC10aWNrZXQtZ3VhcmQtcmVlLXB1YmxpYy1rZXkiOiJCSTBZcGY4WDRSejBhWWZGZmh2S1RpRGdSMkR2ak1XbTRqSUZFTUdJeGRzdmh2Q2tONkxVYVlDSGJkcnpVaEFyYlVhUUh0YTZMcmdQY0NDek9XZG8xTjg9IiwiYmQtdGlja2V0LWd1YXJkLXdlYi12ZXJzaW9uIjoyfQ%3D%3D; cc_request_t=6dbd5150-e6e7-489d-9be0-c9745ef79774; msToken=0b3t_pL0qqnx1_OjB9nbqTPqxfv-EIGH7WkHugPLgqpAVNElH_n_Y7yFnWjWs0H4f_vOZGcMs5s5Xm2XhPw8FaL3y1vIlj1ro9gp5ZAi6oMoWBSuwt-lc3IrpGRF; passport_fe_beating_status=true"
            "Cookie": self.get_cookies()

        }

        info_data = pd.read_sql("select * from data_warehouse_douyin_aweme_info_data where douyin_account_id='{}'".format(douyin_unique_id),self.mysql_config())
        data_list = [info_data.loc[i].to_dict() for i in info_data.index.values]

        favorite_count_list = []
        for ii in data_list:
            url_trend_map = "https://creator.douyin.com/janus/douyin/creator/data/item_analysis/metrics_trend?aid=2906&app_name=aweme_creator_platform&device_platform=web&referer=https:%2F%2Fcreator.douyin.com%2Fcreator-micro%2Fcontent%2Fmanage&user_agent=Mozilla%2F5.0+(Macintosh%3B+Intel+Mac+OS+X+10_15_7)+AppleWebKit%2F537.36+(KHTML,+like+Gecko)+Chrome%2F129.0.0.0+Safari%2F537.36&cookie_enabled=true&screen_width=1512&screen_height=982&browser_language=zh-CN&browser_platform=MacIntel&browser_name=Mozilla&browser_version=5.0+(Macintosh%3B+Intel+Mac+OS+X+10_15_7)+AppleWebKit%2F537.36+(KHTML,+like+Gecko)+Chrome%2F129.0.0.0+Safari%2F537.36&browser_online=true&timezone_name=Asia%2FShanghai&trend_type=2&metrics_group=[0]"
            map_list = ["cover_click_rate","favorite_count","danmaku_count"]

            # t_map_list = []
            data_map = {
                "time_unit": type_time,
                "item_id": ii["aweme_id"],
                "metrics": ["favorite_count"]
            }

            """封面点击率、弹幕量、收藏量计算"""
            # try:

            final_data = requests.get(url_trend_map, headers=headers, params=data_map)
            # print(final_data.json()["trend_map"])
            if 'trend_map' not in final_data.json():
                print(ii,"*********")
            else:

                favorite_count_list.append({'favorite_count':final_data.json()['trend_map']['favorite_count']['0']})
            # except:
            #     favorite_count_list.append({'favorite_count':''})

        cover_click_rate_frame = pd.DataFrame(favorite_count_list)
        cover_click_rate_frame = cover_click_rate_frame.astype("str")
        return cover_click_rate_frame

    def work_detail_play_danmaku_count(self,douyin_unique_id,type_time):
        headers = {
            # "Cookie": "bd_ticket_guard_client_web_domain=2; passport_csrf_token=977a960ef4aa9d0b834baa86d9b58842; passport_csrf_token_default=977a960ef4aa9d0b834baa86d9b58842; ttcid=20cfd75cc9864eb1a0fc4942b37dd83551; s_v_web_id=verify_m18w1lgb_pO620Vb4_cHJk_4wuH_84rw_40gjup8tUVXr; passport_mfa_token=CjXFqvCUDzN0XqIwhxrxUZhyedJhjnPSaOq2qRTLO7mVmAttyrRmSJuVvQtKu1VK3kENFwp6%2BRpKCjx%2F25E%2B6i1CkYs3zReZmeDc9UNG6uNxqWTOcA06H3PxUbYvQ7f%2FmifyHb5O3VnkyOkBX2lnSbTSRlmbyZsQ38HcDRj2sdFsIAIiAQNGaVhf; d_ticket=25c5cbed197e966110d4feebd8d8119c5d194; passport_assist_user=Cjwau2ar_NwRLC2ixe_dhXEsoz57I0-vvhjHeqzg7NGSJb3XgxozLXMU6o6X4ZX-i0w3fSwZVQNc8dKuF3oaSgo8YYC0Rbvz-jb5IOJxnIzJG1_NJ7Wf--FKRoEBxu0HCHsSlN7jKH1Zbo3BncH8ulFtJGlSYTG_nXnV2EUFENLB3A0Yia_WVCABIgEDcI9m4A%3D%3D; n_mh=BLcApAJyDWG0xwZfiZl8GXR4GDSJyJhMmvIui04m6B4; sso_uid_tt=8a4a808434dbc695981896bb660f48a7; sso_uid_tt_ss=8a4a808434dbc695981896bb660f48a7; toutiao_sso_user=21e6ffb53c4c52418cbfd8df8ad99424; toutiao_sso_user_ss=21e6ffb53c4c52418cbfd8df8ad99424; sid_ucp_sso_v1=1.0.0-KDJjNDk0MTRhZTE0OWUxZDZjNjYzZmQzNzkwNzhkMTgzZmY5MWFiNzEKHwjP0NbwvgIQyP6utwYY2hYgDDCZg-HSBTgGQPQHSAYaAmxmIiAyMWU2ZmZiNTNjNGM1MjQxOGNiZmQ4ZGY4YWQ5OTQyNA; ssid_ucp_sso_v1=1.0.0-KDJjNDk0MTRhZTE0OWUxZDZjNjYzZmQzNzkwNzhkMTgzZmY5MWFiNzEKHwjP0NbwvgIQyP6utwYY2hYgDDCZg-HSBTgGQPQHSAYaAmxmIiAyMWU2ZmZiNTNjNGM1MjQxOGNiZmQ4ZGY4YWQ5OTQyNA; passport_auth_status=92a184ef93782587f77f96f0d3e3a4a4%2C; passport_auth_status_ss=92a184ef93782587f77f96f0d3e3a4a4%2C; uid_tt=df03ed6673711ac4872b97f699d2c126; uid_tt_ss=df03ed6673711ac4872b97f699d2c126; sid_tt=00f18b0d75ccbf358fca0db35098911f; sessionid=00f18b0d75ccbf358fca0db35098911f; sessionid_ss=00f18b0d75ccbf358fca0db35098911f; is_staff_user=false; oc_login_type=LOGIN_PERSON; _bd_ticket_crypt_doamin=2; _bd_ticket_crypt_cookie=99b7db74019f20ab7bf1d86e143f2b35; __security_server_data_status=1; sid_guard=00f18b0d75ccbf358fca0db35098911f%7C1726725964%7C5183999%7CMon%2C+18-Nov-2024+06%3A06%3A03+GMT; sid_ucp_v1=1.0.0-KDIxNDQyNWE2MTUxNjllZTE1NWY2OTFmMjE4NGQ5OGI1YmM3ZTU0YzEKGQjP0NbwvgIQzP6utwYY2hYgDDgGQPQHSAQaAmxxIiAwMGYxOGIwZDc1Y2NiZjM1OGZjYTBkYjM1MDk4OTExZg; ssid_ucp_v1=1.0.0-KDIxNDQyNWE2MTUxNjllZTE1NWY2OTFmMjE4NGQ5OGI1YmM3ZTU0YzEKGQjP0NbwvgIQzP6utwYY2hYgDDgGQPQHSAQaAmxxIiAwMGYxOGIwZDc1Y2NiZjM1OGZjYTBkYjM1MDk4OTExZg; _tea_utm_cache_2906=undefined; UIFID_TEMP=63dd93172fb5fdaa8078f33ae0ba1106c8e3f1f870eecbc4b54b497d452c351705d991b09847cfb4e77e5c449a51f49580f9c5a16670964a5380bc79407b9b74366582d28cba029ddf57a51322e5eca6; hevc_supported=true; home_can_add_dy_2_desktop=%220%22; stream_recommend_feed_params=%22%7B%5C%22cookie_enabled%5C%22%3Atrue%2C%5C%22screen_width%5C%22%3A1512%2C%5C%22screen_height%5C%22%3A982%2C%5C%22browser_online%5C%22%3Atrue%2C%5C%22cpu_core_num%5C%22%3A10%2C%5C%22device_memory%5C%22%3A8%2C%5C%22downlink%5C%22%3A10%2C%5C%22effective_type%5C%22%3A%5C%224g%5C%22%2C%5C%22round_trip_time%5C%22%3A150%7D%22; volume_info=%7B%22isUserMute%22%3Afalse%2C%22isMute%22%3Afalse%2C%22volume%22%3A0.5%7D; store-region=cn-zj; store-region-src=uid; odin_tt=a8f584d06370ff960297a6cb064b9eb953f6d5f44874216d54cf89851d2451e465ff49b57cd04e9f5ef43f01ffd4b0f7; csrf_session_id=339011d5e7f539f9baa9fb3c254d7c53; tt_scid=MaocLE5kcYrce7cj1eL-HE6Re1PpGoIFwvWb2IUmJeJ53NcUl.o0g2Fsx6VG.DRt24bd; ttwid=1%7CTRAXmhXVJXjlgpVEpz7ZDIldcnMWzxLd67SkLly3k9o%7C1728566677%7C3d98367cc7d5e8250db3fe9de047b53fa3fc4777ccb9b314c64134bb412901da; bd_ticket_guard_client_data=eyJiZC10aWNrZXQtZ3VhcmQtdmVyc2lvbiI6MiwiYmQtdGlja2V0LWd1YXJkLWl0ZXJhdGlvbi12ZXJzaW9uIjoxLCJiZC10aWNrZXQtZ3VhcmQtcmVlLXB1YmxpYy1rZXkiOiJCSTBZcGY4WDRSejBhWWZGZmh2S1RpRGdSMkR2ak1XbTRqSUZFTUdJeGRzdmh2Q2tONkxVYVlDSGJkcnpVaEFyYlVhUUh0YTZMcmdQY0NDek9XZG8xTjg9IiwiYmQtdGlja2V0LWd1YXJkLXdlYi12ZXJzaW9uIjoyfQ%3D%3D; cc_request_t=6dbd5150-e6e7-489d-9be0-c9745ef79774; msToken=0b3t_pL0qqnx1_OjB9nbqTPqxfv-EIGH7WkHugPLgqpAVNElH_n_Y7yFnWjWs0H4f_vOZGcMs5s5Xm2XhPw8FaL3y1vIlj1ro9gp5ZAi6oMoWBSuwt-lc3IrpGRF; passport_fe_beating_status=true"
            "Cookie": self.get_cookies()

        }

        info_data = pd.read_sql("select * from data_warehouse_douyin_aweme_info_data where douyin_account_id='{}'".format(douyin_unique_id),self.mysql_config())
        data_list = [info_data.loc[i].to_dict() for i in info_data.index.values]

        danmaku_count_list = []
        for ii in data_list:
            url_trend_map = "https://creator.douyin.com/janus/douyin/creator/data/item_analysis/metrics_trend?aid=2906&app_name=aweme_creator_platform&device_platform=web&referer=https:%2F%2Fcreator.douyin.com%2Fcreator-micro%2Fcontent%2Fmanage&user_agent=Mozilla%2F5.0+(Macintosh%3B+Intel+Mac+OS+X+10_15_7)+AppleWebKit%2F537.36+(KHTML,+like+Gecko)+Chrome%2F129.0.0.0+Safari%2F537.36&cookie_enabled=true&screen_width=1512&screen_height=982&browser_language=zh-CN&browser_platform=MacIntel&browser_name=Mozilla&browser_version=5.0+(Macintosh%3B+Intel+Mac+OS+X+10_15_7)+AppleWebKit%2F537.36+(KHTML,+like+Gecko)+Chrome%2F129.0.0.0+Safari%2F537.36&browser_online=true&timezone_name=Asia%2FShanghai&trend_type=2&metrics_group=[0]"
            map_list = ["cover_click_rate","favorite_count","danmaku_count"]

            # t_map_list = []
            data_map = {
                "time_unit": type_time,
                "item_id": ii["aweme_id"],
                "metrics": ["danmaku_count"]
            }

            """封面点击率、弹幕量、收藏量计算"""

            final_data = requests.get(url_trend_map, headers=headers, params=data_map)
            if 'trend_map' not in final_data.json():
                pass
            else:

                aaaa = {'danmaku_count':final_data.json()['trend_map']["danmaku_count"]['0'],'aweme_id':ii["aweme_id"]}
                danmaku_count_list.append(aaaa)

        cover_click_rate_frame = pd.DataFrame(danmaku_count_list)
        cover_click_rate_frame = cover_click_rate_frame.astype("str")
        return cover_click_rate_frame



    def work_list_total(self,douyin_unique_id):
        try:
            current_time = datetime.datetime.now()
            ##当前时间
            formatted_date = current_time.strftime('%Y-%m-%d')

            # 计算往前推90天的时间
            time_90_days_ago = current_time - datetime.timedelta(days=90)

            # 格式化日期为 '%Y-%m-%d' 字符串 90天前的时间
            formatted_date_90_days_ago = time_90_days_ago.strftime('%Y-%m-%d')

            data = {'end_date':formatted_date,
                    'start_date':formatted_date_90_days_ago}
            headers = {
                "Cookie": self.get_cookies()

            }
            url = 'https://creator.douyin.com/dp/douyin/v1/creator/item/item_list'
            work_list_json = requests.post(url,json=data,headers=headers)
            work_list_value_list = []
            for i in work_list_json.json()["data"]:
                # douyin_id = {"douyin_account_id":douyin_unique_id}
                # work_list_json.json()["data"][i].update(douyin_id)
                work_list_value_list.append(work_list_json.json()["data"][i])
            # print(work_list_value_list)
            work_list_frame = pd.DataFrame(work_list_value_list)
            work_list_frame.rename(columns={"item_id":"douyin_video_id","play":"play_count","avg_play_time":"avg_play_count","finish_ratio":"completion_rate",
                                            "like":"like_count","share":"share_count","comment":"comment_count"},inplace=True)

            print(work_list_frame)
            work_list_frame["douyin_account_id"] = douyin_unique_id
            work_list_frame["collect_count"] = ''
            work_list_frame["barrage_count"] = ''
            work_list_frame["create_time"] = str(current_time)
            work_list_frame = work_list_frame[["douyin_account_id","douyin_video_id","play_count","avg_play_count","completion_rate","like_count","share_count","comment_count","net_fans","create_time","collect_count","barrage_count"]]
            delete_sql_trends = "DELETE FROM data_warehouse_douyin_video_contribute_by_total_data WHERE douyin_account_id='{}'".format(
                douyin_unique_id)
            self.pymysql_fetchall(delete_sql_trends)
            pd.io.sql.to_sql(work_list_frame, 'data_warehouse_douyin_video_contribute_by_total_data', self.mysql_config(),
                             schema='data-warehouse',
                             if_exists='append',
                             index=False)
        except:
            print("没有投稿作品")
            # return "没有投稿作品,投稿列表为空"


    def work_type_2(self,douyin_unique_id):
        type_time = 2
        work_detail_by_day_play_count = self.work_detail_by_day_play_count(douyin_unique_id, type_time)
        work_detail_by_day_play_avg_time = self.work_detail_play_avg_time(douyin_unique_id, type_time)
        work_detail_by_day_work_detail_play_finish_ratio = self.work_detail_play_finish_ratio(douyin_unique_id,
                                                                                              type_time)
        work_detail_by_day_work_detail_play_digg_count = self.work_detail_play_digg_count(douyin_unique_id, type_time)
        work_detail_by_day_work_detail_play_comment_count = self.work_detail_play_comment_count(douyin_unique_id,
                                                                                                type_time)
        work_detail_by_day_work_detail_play_share_count = self.work_detail_play_share_count(douyin_unique_id, type_time)
        work_detail_by_day_work_detail_play_new_fans_count = self.work_detail_play_new_fans_count(douyin_unique_id,
                                                                                                  type_time)
        total_frame_seven_result = pd.concat([work_detail_by_day_play_count, work_detail_by_day_play_avg_time,
                                              work_detail_by_day_work_detail_play_finish_ratio,
                                              work_detail_by_day_work_detail_play_digg_count,
                                              work_detail_by_day_work_detail_play_comment_count,
                                              work_detail_by_day_work_detail_play_share_count,
                                              work_detail_by_day_work_detail_play_new_fans_count], axis=1)

        delete_sql_trends = "DELETE FROM detail_seven_result WHERE douyin_account_id='{}'".format(
            douyin_unique_id)
        self.pymysql_fetchall(delete_sql_trends)
        pd.io.sql.to_sql(total_frame_seven_result, 'detail_seven_result',
                         self.mysql_config(),
                         schema='data-warehouse',
                         if_exists='append',
                         index=False)

    def work_type_1(self,douyin_unique_id):
        type_time = 1
        work_detail_by_day_play_count = self.work_detail_by_day_play_count(douyin_unique_id, type_time)
        work_detail_by_day_play_avg_time = self.work_detail_play_avg_time(douyin_unique_id, type_time)
        work_detail_by_day_work_detail_play_finish_ratio = self.work_detail_play_finish_ratio(douyin_unique_id,
                                                                                              type_time)
        work_detail_by_day_work_detail_play_digg_count = self.work_detail_play_digg_count(douyin_unique_id, type_time)
        work_detail_by_day_work_detail_play_comment_count = self.work_detail_play_comment_count(douyin_unique_id,
                                                                                                type_time)
        work_detail_by_day_work_detail_play_share_count = self.work_detail_play_share_count(douyin_unique_id, type_time)
        work_detail_by_day_work_detail_play_new_fans_count = self.work_detail_play_new_fans_count(douyin_unique_id,
                                                                                                  type_time)
        total_frame_seven_result = pd.concat([work_detail_by_day_play_count, work_detail_by_day_play_avg_time,
                                              work_detail_by_day_work_detail_play_finish_ratio,
                                              work_detail_by_day_work_detail_play_digg_count,
                                              work_detail_by_day_work_detail_play_comment_count,
                                              work_detail_by_day_work_detail_play_share_count,
                                              work_detail_by_day_work_detail_play_new_fans_count], axis=1)

        delete_sql_trends = "DELETE FROM detail_seven_result_1 WHERE douyin_account_id='{}'".format(
            douyin_unique_id)
        self.pymysql_fetchall(delete_sql_trends)
        pd.io.sql.to_sql(total_frame_seven_result, 'detail_seven_result_1',
                         self.mysql_config(),
                         schema='data-warehouse',
                         if_exists='append',
                         index=False)

    def work_type_2_three(self,douyin_unique_id,):

        type_time = 1

        work_detail_play_cover_click_rate = self.work_detail_play_cover_click_rate(douyin_unique_id,type_time)
        work_detail_play_cover_favorite_count = self.work_detail_play_favorite_count(douyin_unique_id,type_time)
        work_detail_play_danmaku_count = self.work_detail_play_danmaku_count(douyin_unique_id,type_time)
        total_frame_three_result = pd.concat([work_detail_play_cover_click_rate, work_detail_play_cover_favorite_count,
                                              work_detail_play_danmaku_count], axis=1)

        pd.io.sql.to_sql(total_frame_three_result, 'detail_three_result',
                         self.mysql_config(),
                         schema='data-warehouse',
                         if_exists='append',
                         index=False)


    def seven_json_default(self,douyin_unique_id):

        delete_sql_trends = "DELETE FROM by_day_test_1 WHERE douyin_account_id='{}'".format(
            douyin_unique_id)
        self.pymysql_fetchall(delete_sql_trends)
        data = pd.read_sql("select * from detail_seven_result where douyin_account_id='{}'".format(douyin_unique_id),self.mysql_config())

        data_list = [data.loc[i].to_dict() for i in data.index.values]
        for i in data_list:
            info_json_play_count = {"aweme_id":i["aweme_id"],"douyin_account_id":i["douyin_account_id"],"index_type_key":"play_count"}
            play_count = ast.literal_eval(i["play_count"])
            info_json_play_avg_time = {"aweme_id":i["aweme_id"],"douyin_account_id":i["douyin_account_id"],"index_type_key": "play_avg_time"}
            play_avg_time = ast.literal_eval(i["play_avg_time"])
            info_json_play_finish_ratio = {"aweme_id":i["aweme_id"],"douyin_account_id":i["douyin_account_id"],"index_type_key": "play_finish_ratio"}
            play_finish_ratio = ast.literal_eval(i["play_finish_ratio"])

            info_json_play_digg_count = {"aweme_id": i["aweme_id"], "douyin_account_id": i["douyin_account_id"],"index_type_key": "digg_count"}
            play_digg_count = ast.literal_eval(i["digg_count"])

            info_json_play_comment_count = {"aweme_id": i["aweme_id"], "douyin_account_id": i["douyin_account_id"],
                                         "index_type_key": "comment_count"}
            play_comment_count = ast.literal_eval(i["comment_count"])

            info_json_play_share_count = {"aweme_id": i["aweme_id"], "douyin_account_id": i["douyin_account_id"],
                                            "index_type_key": "share_count"}
            play_share_count = ast.literal_eval(i["share_count"])

            info_json_play_new_fans_count = {"aweme_id": i["aweme_id"], "douyin_account_id": i["douyin_account_id"],
                                          "index_type_key": "new_fans_count"}
            play_new_fans_count = ast.literal_eval(i["new_fans_count"])

            for a,b,c,d,e,f,g in zip(play_count,play_avg_time,play_finish_ratio,play_digg_count,play_comment_count,play_share_count,play_new_fans_count):
                del a["value"]
                del a["xigua_value"]
                del b["value"]
                del b["xigua_value"]
                del d["value"]
                del d["xigua_value"]
                del e["value"]
                del e["xigua_value"]
                del f["value"]
                del f["xigua_value"]
                del g["value"]
                del g["xigua_value"]

                a.update(info_json_play_count)
                b.update(info_json_play_avg_time)
                c.update(info_json_play_finish_ratio)
                d.update(info_json_play_digg_count)
                e.update(info_json_play_comment_count)
                f.update(info_json_play_share_count)
                g.update(info_json_play_new_fans_count)
                # print(b)

                a_frame = pd.DataFrame([a])
                # pd.io.sql.to_sql(a_frame, 'by_day_test_1',
                #                                          self.mysql_config(),
                #                                          schema='data-warehouse',
                #                                          if_exists='append',
                #                                          index=False)
                b_frame = pd.DataFrame([b])

                # pd.io.sql.to_sql(b_frame, 'by_day_test_1',
                #                  self.mysql_config(),
                #                  schema='data-warehouse',
                #                  if_exists='append',
                #                  index=False)
                c_frame = pd.DataFrame([c])

                c_frame.rename(columns={"value":"douyin_value"},inplace=True)

                d_frame = pd.DataFrame([d])
                e_frame = pd.DataFrame([e])
                f_frame = pd.DataFrame([f])
                g_frame = pd.DataFrame([g])
                # total = pd.concat([a,b,c,d,e,f,g],axis=0)

                pd.io.sql.to_sql(a_frame, 'by_day_test_1',
                                 self.mysql_config(),
                                 schema='data-warehouse',
                                 if_exists='append',
                                 index=False)
                pd.io.sql.to_sql(b_frame, 'by_day_test_1',
                                 self.mysql_config(),
                                 schema='data-warehouse',
                                 if_exists='append',
                                 index=False)
                # c_frame["play_finish_ratio"] = c_frame.astype("float")
                pd.io.sql.to_sql(c_frame, 'by_day_test_1',
                                 self.mysql_config(),
                                 schema='data-warehouse',
                                 if_exists='append',
                                 index=False)
                pd.io.sql.to_sql(d_frame, 'by_day_test_1',
                                 self.mysql_config(),
                                 schema='data-warehouse',
                                 if_exists='append',
                                 index=False)
                pd.io.sql.to_sql(e_frame, 'by_day_test_1',
                                 self.mysql_config(),
                                 schema='data-warehouse',
                                 if_exists='append',
                                 index=False)
                pd.io.sql.to_sql(f_frame, 'by_day_test_1',
                                 self.mysql_config(),
                                 schema='data-warehouse',
                                 if_exists='append',
                                 index=False)
                pd.io.sql.to_sql(g_frame, 'by_day_test_1',
                                 self.mysql_config(),
                                 schema='data-warehouse',
                                 if_exists='append',
                                 index=False)
    def seven_json_default_1(self,douyin_unique_id):

        delete_sql_trends = "DELETE FROM by_day_test_2 WHERE douyin_account_id='{}'".format(
            douyin_unique_id)
        self.pymysql_fetchall(delete_sql_trends)
        data = pd.read_sql("select * from detail_seven_result_1 where douyin_account_id='{}'".format(douyin_unique_id),self.mysql_config())

        data_list = [data.loc[i].to_dict() for i in data.index.values]
        for i in data_list:
            info_json_play_count = {"aweme_id":i["aweme_id"],"douyin_account_id":i["douyin_account_id"],"index_type_key":"play_count"}
            play_count = ast.literal_eval(i["play_count"])
            info_json_play_avg_time = {"aweme_id":i["aweme_id"],"douyin_account_id":i["douyin_account_id"],"index_type_key": "play_avg_time"}
            play_avg_time = ast.literal_eval(i["play_avg_time"])
            info_json_play_finish_ratio = {"aweme_id":i["aweme_id"],"douyin_account_id":i["douyin_account_id"],"index_type_key": "play_finish_ratio"}
            play_finish_ratio = ast.literal_eval(i["play_finish_ratio"])

            info_json_play_digg_count = {"aweme_id": i["aweme_id"], "douyin_account_id": i["douyin_account_id"],"index_type_key": "digg_count"}
            play_digg_count = ast.literal_eval(i["digg_count"])

            info_json_play_comment_count = {"aweme_id": i["aweme_id"], "douyin_account_id": i["douyin_account_id"],
                                         "index_type_key": "comment_count"}
            play_comment_count = ast.literal_eval(i["comment_count"])

            info_json_play_share_count = {"aweme_id": i["aweme_id"], "douyin_account_id": i["douyin_account_id"],
                                            "index_type_key": "share_count"}
            play_share_count = ast.literal_eval(i["share_count"])

            info_json_play_new_fans_count = {"aweme_id": i["aweme_id"], "douyin_account_id": i["douyin_account_id"],
                                          "index_type_key": "new_fans_count"}
            play_new_fans_count = ast.literal_eval(i["new_fans_count"])

            for a,b,c,d,e,f,g in zip(play_count,play_avg_time,play_finish_ratio,play_digg_count,play_comment_count,play_share_count,play_new_fans_count):
                del a["value"]
                del a["xigua_value"]
                del b["value"]
                del b["xigua_value"]
                del d["value"]
                del d["xigua_value"]
                del e["value"]
                del e["xigua_value"]
                del f["value"]
                del f["xigua_value"]
                del g["value"]
                del g["xigua_value"]

                a.update(info_json_play_count)
                b.update(info_json_play_avg_time)
                c.update(info_json_play_finish_ratio)
                d.update(info_json_play_digg_count)
                e.update(info_json_play_comment_count)
                f.update(info_json_play_share_count)
                g.update(info_json_play_new_fans_count)
                # print(b)

                a_frame = pd.DataFrame([a])
                # pd.io.sql.to_sql(a_frame, 'by_day_test_1',
                #                                          self.mysql_config(),
                #                                          schema='data-warehouse',
                #                                          if_exists='append',
                #                                          index=False)
                b_frame = pd.DataFrame([b])

                # pd.io.sql.to_sql(b_frame, 'by_day_test_1',
                #                  self.mysql_config(),
                #                  schema='data-warehouse',
                #                  if_exists='append',
                #                  index=False)
                c_frame = pd.DataFrame([c])

                c_frame.rename(columns={"value":"douyin_value"},inplace=True)

                d_frame = pd.DataFrame([d])
                e_frame = pd.DataFrame([e])
                f_frame = pd.DataFrame([f])
                g_frame = pd.DataFrame([g])
                # total = pd.concat([a,b,c,d,e,f,g],axis=0)

                pd.io.sql.to_sql(a_frame, 'by_day_test_2',
                                 self.mysql_config(),
                                 schema='data-warehouse',
                                 if_exists='append',
                                 index=False)
                pd.io.sql.to_sql(b_frame, 'by_day_test_2',
                                 self.mysql_config(),
                                 schema='data-warehouse',
                                 if_exists='append',
                                 index=False)
                # c_frame["play_finish_ratio"] = c_frame.astype("float")
                pd.io.sql.to_sql(c_frame, 'by_day_test_2',
                                 self.mysql_config(),
                                 schema='data-warehouse',
                                 if_exists='append',
                                 index=False)
                pd.io.sql.to_sql(d_frame, 'by_day_test_2',
                                 self.mysql_config(),
                                 schema='data-warehouse',
                                 if_exists='append',
                                 index=False)
                pd.io.sql.to_sql(e_frame, 'by_day_test_2',
                                 self.mysql_config(),
                                 schema='data-warehouse',
                                 if_exists='append',
                                 index=False)
                pd.io.sql.to_sql(f_frame, 'by_day_test_2',
                                 self.mysql_config(),
                                 schema='data-warehouse',
                                 if_exists='append',
                                 index=False)
                pd.io.sql.to_sql(g_frame, 'by_day_test_2',
                                 self.mysql_config(),
                                 schema='data-warehouse',
                                 if_exists='append',
                                 index=False)


    def three_json_default(self,douyin_unique_id):


        data = pd.read_sql("select * from detail_three_result where douyin_account_id='{}'".format(douyin_unique_id),self.mysql_config())

        data_list = [data.loc[i].to_dict() for i in data.index.values]
        for i in data_list:
            # print(i)
            cover_click_rate_json = {"aweme_id":i["aweme_id"],"douyin_account_id":i["douyin_account_id"],"index_type_key":"cover_click_rate"}
            cover_click_rate = ast.literal_eval(i["cover_click_rate"])
            # print(cover_click_rate)

            # favorite_count_json = {"aweme_id": i["aweme_id"], "douyin_account_id": i["douyin_account_id"],
            #                         "index_type_key": "favorite_count"}

            # if i["favorite_count"]!='':

                # favorite_count = ast.literal_eval(i["favorite_count"])
            # else:
            #     favorite_count = []
            # if i["danmaku_count"]!='':

                # danmaku_count_json = {"aweme_id": i["aweme_id"], "douyin_account_id": i["douyin_account_id"],
                #                        "index_type_key": "danmaku_count"}
                # danmaku_count = ast.literal_eval(i["danmaku_count"])
            # else:
            #     danmaku_count =[]

            # print(danmaku_count)

            for a in cover_click_rate:
                a.update(cover_click_rate_json)
                a_frame = pd.DataFrame([a])
                a_frame.rename(columns={"value":"douyin_value","date_time":"key"},inplace=True)
                print(a_frame.columns)
                pd.io.sql.to_sql(a_frame, 'by_day_test_1',
                                                             self.mysql_config(),
                                                             schema='data-warehouse',
                                                             if_exists='append',
                                                             index=False)


            # print(cover_click_rate[0])
            # info_json_play_avg_time = {"aweme_id":i["aweme_id"],"douyin_account_id":i["douyin_account_id"],"index_type_key": "play_avg_time"}
            # play_avg_time = ast.literal_eval(i["play_avg_time"])
            # info_json_play_finish_ratio = {"aweme_id":i["aweme_id"],"douyin_account_id":i["douyin_account_id"],"index_type_key": "play_finish_ratio"}
            # play_finish_ratio = ast.literal_eval(i["play_finish_ratio"])
            #
            # info_json_play_digg_count = {"aweme_id": i["aweme_id"], "douyin_account_id": i["douyin_account_id"],"index_type_key": "digg_count"}
            # play_digg_count = ast.literal_eval(i["digg_count"])
            #
            # info_json_play_comment_count = {"aweme_id": i["aweme_id"], "douyin_account_id": i["douyin_account_id"],
            #                              "index_type_key": "comment_count"}
            # play_comment_count = ast.literal_eval(i["comment_count"])
            #
            # info_json_play_share_count = {"aweme_id": i["aweme_id"], "douyin_account_id": i["douyin_account_id"],
            #                                 "index_type_key": "share_count"}
            # play_share_count = ast.literal_eval(i["share_count"])
            #
            # info_json_play_new_fans_count = {"aweme_id": i["aweme_id"], "douyin_account_id": i["douyin_account_id"],
            #                               "index_type_key": "new_fans_count"}
            # play_new_fans_count = ast.literal_eval(i["new_fans_count"])
            #
            # for a,b,c,d,e,f,g in zip(play_count,play_avg_time,play_finish_ratio,play_digg_count,play_comment_count,play_share_count,play_new_fans_count):
            #     del a["value"]
            #     del a["xigua_value"]
            #     del b["value"]
            #     del b["xigua_value"]
            #     del d["value"]
            #     del d["xigua_value"]
            #     del e["value"]
            #     del e["xigua_value"]
            #     del f["value"]
            #     del f["xigua_value"]
            #     del g["value"]
            #     del g["xigua_value"]
            #
                # a.update(info_json_play_count)
            #     b.update(info_json_play_avg_time)
            #     c.update(info_json_play_finish_ratio)
            #     d.update(info_json_play_digg_count)
            #     e.update(info_json_play_comment_count)
            #     f.update(info_json_play_share_count)
            #     g.update(info_json_play_new_fans_count)
            #     # print(b)
            #
            #     a_frame = pd.DataFrame([a])
            #     # pd.io.sql.to_sql(a_frame, 'by_day_test_1',
            #     #                                          self.mysql_config(),
            #     #                                          schema='data-warehouse',
            #     #                                          if_exists='append',
            #     #                                          index=False)
            #     b_frame = pd.DataFrame([b])
            #
            #     # pd.io.sql.to_sql(b_frame, 'by_day_test_1',
            #     #                  self.mysql_config(),
            #     #                  schema='data-warehouse',
            #     #                  if_exists='append',
            #     #                  index=False)
            #     c_frame = pd.DataFrame([c])
            #
            #     c_frame.rename(columns={"value":"douyin_value"},inplace=True)
            #
            #     d_frame = pd.DataFrame([d])
            #     e_frame = pd.DataFrame([e])
            #     f_frame = pd.DataFrame([f])
            #     g_frame = pd.DataFrame([g])
            #     # total = pd.concat([a,b,c,d,e,f,g],axis=0)
            #
            #     pd.io.sql.to_sql(a_frame, 'by_day_test_1',
            #                      self.mysql_config(),
            #                      schema='data-warehouse',
            #                      if_exists='append',
            #                      index=False)
            #     pd.io.sql.to_sql(b_frame, 'by_day_test_1',
            #                      self.mysql_config(),
            #                      schema='data-warehouse',
            #                      if_exists='append',
            #                      index=False)
            #     # c_frame["play_finish_ratio"] = c_frame.astype("float")
            #     pd.io.sql.to_sql(c_frame, 'by_day_test_1',
            #                      self.mysql_config(),
            #                      schema='data-warehouse',
            #                      if_exists='append',
            #                      index=False)
            #     pd.io.sql.to_sql(d_frame, 'by_day_test_1',
            #                      self.mysql_config(),
            #                      schema='data-warehouse',
            #                      if_exists='append',
            #                      index=False)
            #     pd.io.sql.to_sql(e_frame, 'by_day_test_1',
            #                      self.mysql_config(),
            #                      schema='data-warehouse',
            #                      if_exists='append',
            #                      index=False)
            #     pd.io.sql.to_sql(f_frame, 'by_day_test_1',
            #                      self.mysql_config(),
            #                      schema='data-warehouse',
            #                      if_exists='append',
            #                      index=False)
            #     pd.io.sql.to_sql(g_frame, 'by_day_test_1',
            #                      self.mysql_config(),
            #                      schema='data-warehouse',
            #                      if_exists='append',
            #                      index=False)


    def dy_creator_center_spider(self):

        print('用户:',self.user_id, '此次的端口是:',self.PORT)
        self.ch.get('https://creator.douyin.com/creator-micro/home')
        time.sleep(2)
        user_it_exist = self.user_state()
        data_frame = self.user_info()
        if user_it_exist == "用户不存在":
            data_frame["spider_login_code"] = '0'
            pd.io.sql.to_sql(data_frame, 'dy_creator_center', self.mysql_config(), schema='data-warehouse',
                             if_exists='append', index=False)

        # print("走到这里了")
        login_url_is = self.ch.url
        if login_url_is == "https://creator.douyin.com/":
            print("授权登录过期")
            self.ch.close()
            current_time = datetime.datetime.now()
            formatted_date = current_time.strftime('%Y-%m-%d')

            data = pd.read_sql("select douyin_unique_id,nick_name from dy_user_info where dy_spider_user_id='{}'".format(self.user_id),self.mysql_config())
            data = data.values.tolist()[0]
            expire_douyin_unique_id = data[0]
            expire_nick_name = data[1]
            expire_json = {"douyin_spider_user_id": self.user_id, "douyin_account_id": expire_douyin_unique_id,
                           "nick_name": expire_nick_name, "expire_time": formatted_date}
            expire_frame = pd.DataFrame([expire_json])

            pd.io.sql.to_sql(expire_frame, 'dy_expire_account', self.mysql_config(), schema='data-warehouse',
                             if_exists='append', index=False)
            return "授权登录过期"

        else:

            self.redis_conn.set('dy_center_qrcode:{}'.format(self.user_id), '"no_login"')
            self.redis_conn.expire('dy_center_qrcode:{}'.format(self.user_id), 300)


        time.sleep(5)
        """获取抖音创作中心的用户详情"""
        detail_result = self.dy_user_info_detail()
        fans_num = detail_result[0]
        douyin_unique_id = detail_result[1]
        """获取抖音cookies"""
        cookies = self.get_cookies()
        center_data_by_day = self.data_center_video_count_by_day(cookies,douyin_unique_id)
        center_data_seven = self.data_center_video_count_seven(cookies,douyin_unique_id)
        center_data_thirty = self.data_center_video_count_thirty(cookies,douyin_unique_id)
        fans_portrait = self.dy_fans_portrait(fans_num=fans_num,douyin_unique_id=douyin_unique_id)
        if fans_portrait == "粉丝画像计算完成":
            work_list = self.work_manager(douyin_unique_id)
            work_detail = self.work_detail_poi(cookies,douyin_unique_id)
            # work_detail_hour = self.work_detail_hour(cookies,douyin_unique_id)

            self.work_type_2(douyin_unique_id)
            # self.work_type_2_three(douyin_unique_id)
            self.seven_json_default(douyin_unique_id)
            # self.three_json_default(douyin_unique_id)
            print(self.by_day_data_default(douyin_unique_id))
            self.work_type_1(douyin_unique_id)
            self.seven_json_default_1(douyin_unique_id)
            self.by_day_data_default_1(douyin_unique_id)
            work_list_total_def = self.work_list_total(douyin_unique_id)
            # aaa = self.listen_refer()
            # print(aaa)
        return "程序计算完成"

# dy_center = DY_XPIDER(user_id='222224')
# aa = dy_center.dy_creator_center_spider()
def mysql_config():
    user = 'root'
    password = quote_plus('Jison0820')
    # passwd ='merchantsasd123!@%&'
    host = 'rm-bp1be6s3581xp13rfco.mysql.rds.aliyuncs.com'
    # port1 ='3306'
    dbname2 = 'data-warehouse'
    engine2 = create_engine(f"mysql+pymysql://{user}:{password}@{host}:3306/{dbname2}?charset=utf8mb4")
    return engine2

def dy_by_day_job():
    data = pd.read_sql("select * from dy_user_info",mysql_config())
    data_list = [data.loc[i].to_dict() for i in data.index.values]

    for i in data_list:

        dy_center = DY_XPIDER(user_id=i["dy_spider_user_id"])
        aa = dy_center.dy_creator_center_spider()
        print(aa)

    return "抖音创作者中心每日计算完成"

# if __name__ == '__main__':
#     dy_by_day_job()

